带增量部分的 MySQL 触发器

Question

我正在使用 MySQL 数据库，并且希望在对表“jobs”进行任何插入之前创建一个触发器

我的架构如下:

+-------------+     +--------------+
|    jobs     |     |   machines   |
+-------------+     +--------------+
| job_id      |  ___| machine_id   |
| job_machine |_|   | machine_name |
| job_name    |     +--------------+
| job_start   |
+-------------+     

jobs.job_machine和machines.machine_id之间有外键

job_start 是一个日期时间，将用于填充 job_name。但它会被格式化成这样

select DATE_FORMAT(job_start, "%y%m%d") from jobs where NEW.job_id;

我希望 job_name 是 job_start + job_machine + 增量的串联。我会解释一下。

例如，如果插入的职位是

+--------+-------------+-----------------------+----------+
| job_id | job_machine |       job_start       | job_name |
+--------+-------------+-----------------------+----------+
|      1 |           3 | '2015-09-18 14:20:00' |          |
+--------+-------------+-----------------------+----------+

job_name 将是:15091831

但是如果同一天在同一台机器上创建类似的作业，则为 15091832

我有

CREATE DEFINER = CURRENT_USER TRIGGER `myTable`.`jobs_BEFORE_INSERT` BEFORE INSERT ON `jobs` FOR EACH ROW
BEGIN
    select concat((select DATE_FORMAT(job_start, "%y%m%d") from jobs where job_id=NEW.job_id) , (select job_machine from jobs where job_id = NEW.job_id));
END

但是我对增量部分不太确定。最好的方法是什么？

Answer 1

首先确定当前的增量值，然后将其用于 id 和 name:

CREATE DEFINER = CURRENT_USER TRIGGER `myTable`.`jobs_BEFORE_INSERT`
 BEFORE INSERT ON `jobs` FOR EACH ROW
BEGIN
DECLARE next_id integer;
 -- select the current highest id and increment it
SET @next_id := (select max(job_id)+1
    from jobs 
    where DATE_FORMAT(job_start, '%y%m%d') = DATE_FORMAT(NEW.job_start, '%y%m%d') 
        and job_machine = NEW.job_machine);
-- if it is the first job for this machine this day then make it nr 1
IF ISNULL(@next_id) THEN
    SET @next_id := 1;
END IF;
-- then set your new row accordingly
SET NEW.job_id = @next_id;
SET NEW.job_name = concat(DATE_FORMAT(NEW.job_start, '%y%m%d'), job_machine, @next_id);
END