mysql - 使用前缀和整数递增值时触发器出错。

Question

这里我使用触发器自动递增带有前缀字符串的字段“Project_Id”。首先，我检索最后一条记录并将其存储到变量中。之后，将该记录的子字符串存储到另一个变量中，然后转换为整数。

delimiter //
     CREATE OR REPLACE TRIGGER tg_project_master
     BEFORE INSERT ON project_master
     FOR EACH ROW
     BEGIN
     DECLARE selectid VARCHAR(20);
     DECLARE selid VARCHAR(20);
    DECLARE autoi INT DEFAULT 0;
    SET selectid := (select Project_id from project_master order by Project_id desc limit 1);
    SET selid := SUBSTRING_INDEX(selectid,'O',-1);
    SET autoi := 1+ CAST(@selectid AS INT);
    INSERT INTO project_master SET NEW.Project_id = CONCAT('PRJ',@autoi);
END; //
DELIMITER;

将值增加 1 后，我将插入回表(Project_master)中。

这是我遇到的错误。

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER tg_project_master BEFORE INSERT ON project_master FOR EACH ROW BEGIN DEC' at line 1 Line 2, column 1

Answer 1

根据 documentation您不能CREATE OR REPLACE触发器，而只能CREATE它。因此，请执行以下操作:

DROP TRIGGER IF EXISTS tg_project_master;

delimiter //
CREATE TRIGGER tg_project_master
  BEFORE INSERT ON project_master
  FOR EACH ROW
  ....
END; //
DELIMITER;