MySQL触发器逐行校验批量INSERT数据

MySQL触发器处理批量INSERT时，其实是逐行执行的——每插入一行就会触发一次BEFORE INSERT，而且NEW对象只代表当前这一行，没法看到同一批的其他行。原因很简单：MySQL只支持行级触发器（FOR EACH ROW），没有语句级触发器的选项。

换句话说，一次INSERT多行语句会被拆成多次单行触发，每次校验只能基于当前行，无法跨行判断或聚合。

为什么批量INSERT会触发多次校验？

很多人一开始会纳闷：难道不能一次性检查整批数据？答案是MySQL的设计就这么直接——FOR EACH ROW不是可选语法，而是强制要求。就算你写INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c')，也会触发3次BEFORE INSERT，每次NEW.id和NEW.name只对应其中一行。

• 没有“整批上下文”，NEW在每次触发时重置，上一行的值完全不可见
• 不支持类似SQL Server的INSERTED临时表，也无法在触发器里查刚插入的其他行——因为它们还没提交，事务隔离也限制住了
• 如果某行在校验中被SIGNAL中断（比如邮箱格式错误），整条批量语句会回滚，其余行也不会插入

如何用BEFORE INSERT触发器做单行校验？

既然只能基于当前行，那么校验逻辑就必须围绕当前字段展开，不能依赖其他行。下面是一个常见的写法示例：

DELIMITER $$CREATE TRIGGER validate_user_before_insert BEFORE INSERT ON users FOR EACH ROW BEGIN  IF NEW.email NOT LIKE '%_@__%.__%' THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email format';  END IF;  IF NEW.status NOT IN ('active', 'inactive') THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid status value';  END IF;END$$DELIMITER ;

• NEW在BEFORE INSERT中可读可写，适合改写字段，比如自动填充NEW.created_at = NOW()
• 要避免在触发器里SELECT其他表（比如查黑名单），否则批量插入时性能会急剧下降
• 简单模式优先用LIKE而非REGEXP，MySQL 8.0+虽然支持正则，但开销更大

遇到重复数据校验怎么办？

触发器不适合做“本批内去重”，比如防止同一批里出现两个相同的email，因为它看不见同批的其他行。真正可靠的方案是交给数据库本身的约束：

• 建唯一索引：ALTER TABLE users ADD UNIQUE KEY uk_email (email);
• 用INSERT IGNORE跳过冲突行，或者INSERT ... ON DUPLICATE KEY UPDATE做合并
• 如果业务要求“整批要么全过、要么全拒”，就在应用层先查再插，或者用存储过程统一封装事务
• 千万不要试图在触发器里写SELECT COUNT(*) FROM users WHERE email = NEW.email——这只能查已有数据，完全查不到同批其他待插入的行

容易被忽略的生效边界

触发器不是万能拦截器，下面这些情况它根本不会运行：

• LOAD DATA INFILE默认绕过触发器，除非显式启用LOCAL并配置sql_log_bin=ON
• 某些ORM批量操作（比如Hibernate的sa veAll）可能生成INSERT ... SELECT，部分MySQL版本不触发
• 用户账号缺少TRIGGER权限时，创建成功但执行时会报ERROR 1227 (42501)
• 从库默认禁用触发器（sla ve_sql_verify_checksum=OFF等配置影响），主从行为不一致

真正需要跨行逻辑的场景，比如统计本次插入总金额、批量同步到汇总表，就别硬塞进触发器了——移到应用层，或者用存储过程统一控制事务边界和执行顺序。这样既清晰，又不会给自己挖坑。