MySQL触发器用CONCAT拼接自增字段的报错与自增失效问题

1054未知字段报错 #

MySQL触发器中，引用待插入行的字段必须加NEW.前缀。未加前缀的字段名，MySQL会从触发器全局上下文查找，不会读取待插入行的传入值，因此触发未知字段报错，该问题与引号使用无关。
初始触发器中CONCAT(identifiantcorpora,'',id)部分的两个字段都未加NEW.前缀，是报错的直接诱因。

AUTO_INCREMENT失效 #

出现该问题有两个核心原因：

• 现有INSERT语句手动为id字段传入了明确值145，MySQL对AUTO_INCREMENT字段的默认规则是：如果插入时手动传入非空值，会直接使用传入值，不会触发自增生成逻辑，该行为与触发器无关。
• BEFORE INSERT触发器执行阶段，MySQL还未完成AUTO_INCREMENT字段的值生成，此时读取NEW.id拿到的是占位值（通常为0），不仅拼接结果错误，还会干扰自增逻辑的正常执行。

方案1：改用AFTER INSERT触发器（兼容所有MySQL版本） #

BEFORE INSERT阶段拿不到最终生成的自增ID，因此把拼接逻辑移到AFTER INSERT阶段，此时自增值已经生成完成，不会出现取值错误或自增失效问题：

1. 先删除原有错误触发器：

DROP TRIGGER IF EXISTS insert_trigger;

2. 创建新的AFTER INSERT触发器：

DELIMITER //
CREATE TRIGGER insert_trigger
AFTER INSERT ON user_ickle
FOR EACH ROW
BEGIN
    UPDATE user_ickle
    SET identifiantcorporabis = CONCAT(NEW.identifiantcorpora, '', NEW.id)
    WHERE id = NEW.id;
END //
DELIMITER ;

3. 调整INSERT语句，不要手动给自增id字段传固定值，传入NULL即可触发自增逻辑，或明确列出除id外的插入字段、省略id字段：

INSERT INTO `user_ickle` 
VALUES (NULL,'Abkhazian','Afghanistan','Non-binary',45,'Adangme','Achinese','Achinese','Achinese','Achinese','Achinese','0','1','1','Austria - Wirtschafts- und Fremdsprachenakademie Salzburg','Online education has harmed the quality of higher education in Sri Lanka. ','Literary','No timing','No','Yes','lol',NULL,'0',1,'a61ba7fd-4df7-46f7-86f8-3a9ac0eb2b5c',2,3,2022,'12','learneridentifier','test');

方案2：使用生成列（MySQL 5.7+支持，无需触发器，性能更优） #

如果你的MySQL版本是5.7及以上，可以直接用内置的生成列功能实现字段自动拼接，完全避免触发器带来的各种异常：

-- 先删除原有手动创建的拼接字段
ALTER TABLE user_ickle DROP COLUMN identifiantcorporabis;
-- 新增生成列，自动按规则拼接值
ALTER TABLE user_ickle ADD COLUMN identifiantcorporabis VARCHAR(255) 
GENERATED ALWAYS AS (CONCAT(identifiantcorpora, '', id)) STORED;

触发器和SQL语句中的表名需要统一大小写，你之前触发器写的表名是User_ickle，插入语句用的是user_ickle，如果部署在大小写敏感的文件系统（比如Linux默认ext4/xfs）上，两个名称会被识别为不同的表，额外触发报错。

内容的提问来源于stack exchange，提问作者Balkhrod