mysql - 如何在插入之前使用mysql触发器生成动态id

Question

我需要生成student_roll_no，如20191B01;我想增加student_roll_no的最后两位数字，如20191B02，.......20191B10,20191B11。使用以下代码，我可以在 20191B010 之后到达 20191B09，但我需要 20191B10。

SET NEW.student_roll_no = CONCAT(
   YEAR(CURRENT_DATE), 
   NEW.class_code,
   IFNULL(CONCAT('0',(substring((SELECT student_roll_no FROM students WHERE class_code = NEW.class_code ORDER BY created_at DESC LIMIT 1),-2)+1)),'01'))

Answer 1

使用以下代码来解决您的问题。

SET @roll_num = IFNULL((substring((SELECT student_roll_no FROM students WHERE class_code = NEW.class_code ORDER BY created_at DESC LIMIT 1),-2) +1),'1'),
NEW.student_roll_no = CONCAT(YEAR(CURRENT_DATE), NEW.class_code, 
        IF (    
           @roll_num < 10,
           CONCAT('0',@roll_num),
           @roll_num
       )
)