mysql触发器如何实现复杂的业务约束_对比CHECK约束与Trigger优劣-mysql教程-PHP中文网

mysql触发器中禁止使用commit/rollback，校验失败应通过signal抛异常实现自动回滚；before触发器可修改new值，after中无效；优先用check约束单表规则，跨表校验等复杂逻辑才用触发器。

mysql触发器如何实现复杂的业务约束_对比check约束与trigger优劣

MySQL触发器里不能用 COMMIT/ROLLBACK，否则直接报错

想在校验失败时“手动回滚当前操作”，写 ROLLBACK 会触发 ERROR 1305 (42000): FUNCTION does not exist ——这不是函数缺失，而是 MySQL 明确禁止在触发器中做事务控制。触发器天然运行在父语句的事务上下文中，它自己无权开启或结束事务。

正确做法是用 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'xxx' 主动抛异常，让外层 INSERT/UPDATE 自动中断并回滚。注意：MySQL 5.6 及更早版本不支持 SIGNAL，只能靠插入不存在的表（如 INSERT INTO nonexistent_table VALUES()）来间接触发错误，但不可靠、难调试、不推荐。

常见踩坑点：

在存储过程中调用了 COMMIT，又在触发器里调用该过程 → 报错
误以为 AFTER UPDATE 中改 NEW.status 能影响结果 → 实际静默忽略，且不报错
用 SELECT ... FOR UPDATE 做并发校验时没加索引 → 触发器变成全表锁瓶颈

CHECK 约束做不到的事，才轮到触发器上

CHECK 约束只能引用本表字段和确定性表达式，比如 price > 0 或 status IN ('draft', 'published')；它无法查其他表、不能调函数、不支持自定义错误提示。

触发器能做的典型复杂约束：

插入订单前查 users 表确认用户状态为 'active'
更新商品库存时，同步检查关联的 orders 表中是否有未支付订单占用该库存
禁止将已发货订单的 shipping_address 改为 NULL（需对比 OLD.shipping_address 和 NEW.shipping_address）

但要注意：MySQL 8.0.16+ 的 CHECK 约束已支持子查询（带限制），不过仍不如触发器灵活。优先用 CHECK 实现单表、无副作用、高性能的规则，把跨表、状态机、审计类逻辑留给触发器。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

BEFORE vs AFTER：改数据只能在 BEFORE 里生效

只有 BEFORE INSERT 和 BEFORE UPDATE 允许修改 NEW.column_name，比如把空字符串转成 NULL、补默认时间戳、标准化手机号格式。这些修改会影响最终写入的值。

AFTER 类型触发器中对 NEW 赋值完全无效，OLD 在任何时机都不可写（赋值会被 MySQL 静默丢弃，不报错也不生效）。

典型使用场景：

BEFORE INSERT：填充 created_at、生成 UUID、校验邮箱格式（正则匹配）、拦截非法字符
BEFORE UPDATE：防止关键字段被篡改（如 IF OLD.status = 'paid' AND NEW.amount != OLD.amount THEN SIGNAL ...）
AFTER INSERT：写审计日志、发消息到队列表、更新统计缓存（只读访问 NEW.id 等已确定值）

性能和可维护性比想象中更敏感

触发器是逐行执行的。一条 INSERT INTO t1 SELECT * FROM t2 LIMIT 10000 会触发 10000 次触发器逻辑——如果里面带一次没走索引的 SELECT COUNT(*) FROM related_table WHERE x = NEW.y，整体耗时可能从毫秒级飙升到秒级。

线上最容易被忽略的三点：