如何优化此触发器？它运行完美，但我认为可以改进：
CREATE TRIGGER `update_request_missions_after_insert` BEFORE INSERT ON `request_missions`
FOR EACH ROW
IF ((SELECT id FROM request_missions ORDER BY id DESC LIMIT 1)+1) >= 1 AND
((SELECT id FROM request_missions ORDER BY id DESC LIMIT 1)+1) <= 9 THEN
SET New.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%y'), '-',
CONCAT('00000',(SELECT id FROM request_missions ORDER BY id DESC LIMIT 1)+1));
END IF
结果将在rm_number列中，例如：
对于ID> = 1和ID <= 9
19-000001
19-000002
...
19-000009
谢谢Bill M. Bill Karwin，现在的代码更干净了对于一年的问题，我将其更改为'％Y'。现在的代码是：
CREATE TRIGGER `update_request_missions_after_insert` BEFORE INSERT ON `request_missions`
FOR EACH ROW
BEGIN
DECLARE next_id INT;
SELECT COALESCE(MAX(id),0) + 1 INTO next_id FROM request_missions;
IF next_id BETWEEN 1 AND 9 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', '00000', next_id);
ELSEIF next_id BETWEEN 10 AND 99 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', '0000', next_id);
ELSEIF next_id BETWEEN 100 AND 999 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', '000', next_id);
ELSEIF next_id BETWEEN 1000 AND 9999 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', '00', next_id);
ELSEIF next_id BETWEEN 10000 AND 99999 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', '0', next_id);
ELSEIF next_id BETWEEN 100000 AND 999999 THEN
SET NEW.rm_number = CONCAT(DATE_FORMAT(CURDATE(),'%Y'), '-', next_id);
END IF;
END
警告：这有竞争的条件。例如，如果两个会话同时插入到表中，则它们可能都读取相同的next_id。
知道此触发器的目的是为添加的每一行添加一个格式为“ YYYY-ID”的数字吗？YYYY：是当前Year ID：是触发器中所示格式的当前行的ID
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句