SQL数据修改后如何校验数据完整性_利用触发器与约束检查-SQL-PHP中文网

应优先使用外键ON UPDATE CASCADE而非触发器补救，因触发器无法保证事务一致性且易引发死锁；CHECK约束比触发器更轻量高效，仅在需跨表校验或记录日志时用触发器。

sql数据修改后如何校验数据完整性_利用触发器与约束检查

UPDATE 后立刻发现外键不一致怎么办

触发器不是万能的，UPDATE 语句执行成功不代表数据逻辑正确——比如改了主表 user_id，但子表没同步更新，外键约束又设成了 NO ACTION 或 RESTRICT，这时数据库不会报错，但数据已断裂。

真正能拦住这类问题的是外键的 ON UPDATE CASCADE，而不是事后靠触发器“补救”。如果已有表没开这个选项，临时加触发器只是掩盖问题，还可能因触发顺序导致死锁。

检查现有外键行为：SELECT constraint_name, update_rule FROM information_schema.referential_constraints WHERE constraint_schema = 'your_db';
建表时就该明确写：FOREIGN KEY (order_user_id) REFERENCES users(id) ON UPDATE CASCADE
已有表想补？先删外键再重建（注意备份）：ALTER TABLE orders DROP FOREIGN KEY fk_orders_user; ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (order_user_id) REFERENCES users(id) ON UPDATE CASCADE;

用 BEFORE UPDATE 触发器做业务校验的典型陷阱

BEFORE UPDATE 确实适合做字段级检查，比如禁止把 status 从 'shipped' 改回 'pending'，但很多人忽略它不能访问新旧值的完整行镜像（尤其在 MySQL 5.7+ 中 OLD.* 和 NEW.* 是只读的），更麻烦的是：它无法感知事务中其他语句的影响。

别在触发器里查同一张表——会报 Can't update table 'xxx' in stored function/trigger
跨表校验要小心隔离级别：在 REPEATABLE READ 下，触发器看到的可能是快照数据，不是最新状态

示例（安全写法）：

CREATE TRIGGER check_status_transition BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' AND NEW.status != 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot change shipped order status'; END IF; END;

CHECK 约束 vs 触发器：什么时候该选哪个

CHECK 约束轻量、高效、声明式，MySQL 8.0.16+ 和 PostgreSQL 原生支持；触发器灵活但重、难调试、绕过 INSERT ... ON DUPLICATE KEY UPDATE 等语法。别为了“看起来更可控”硬上触发器。

CHECK 能搞定的，绝不写触发器：比如 age BETWEEN 0 AND 150、email LIKE '%@%'
触发器必要场景只有两个：需要查其他表做判断，或需要记录日志/调用外部逻辑
PostgreSQL 注意：CHECK 不校验 NULL，除非显式写 column IS NOT NULL；MySQL 8.0.16+ 的 CHECK 默认不校验 NULL，但可加 NOT VALID 跳过历史数据验证

校验脚本跑完却漏掉脏数据？重点看事务边界和索引覆盖

手动写 SELECT COUNT(*) 对比前后数量，或用 EXCEPT 找差异行，听起来靠谱，但实际常漏掉两类数据：一是被 UNCOMMITTED 修改遮蔽的行（没加 FOR UPDATE 或没设合适隔离级别），二是没走索引的慢查询跳过部分分区（尤其在分表或时间分区场景）。

校验查询必须加 SELECT ... FOR UPDATE 或在 SERIALIZABLE 隔离级别下运行
确认校验字段有索引：比如按 updated_at 查最近修改，但该字段没索引 → 全表扫描 → 超时跳过
分页校验别用 LIMIT OFFSET：大数据量下偏移越往后越慢，改用基于游标的 WHERE updated_at > ? ORDER BY updated_at LIMIT 1000

最常被跳过的其实是多版本并发控制（MVCC）下的“不可见更新”——你查的时候，另一事务刚提交了一条记录，但你的事务快照还没包含它。这事关隔离级别选择，不是加个触发器就能绕过去的。