如何在SQL中优雅地实现根据历史变更自动触发INSERT审计日志？-SQL-PHP中文网

MySQL中UPDATE触发器必须用OLD.column_name显式引用旧值（如OLD.email），不可用SELECT FROM OLD或未加前缀的OLD.id，否则报错；PostgreSQL支持OLD.*但需字段顺序严格匹配，更推荐显式列名。

如何在sql中优雅地实现根据历史变更自动触发insert审计日志？

触发器里写INSERT审计日志，但UPDATE时没捕获old值？

PostgreSQL 和 MySQL 的触发器语法差异大，直接照搬会丢数据。PostgreSQL 的 OLD 在 BEFORE UPDATE 可用，但 MySQL 的 BEFORE UPDATE 里 OLD 是只读的——想记录变更前字段值，必须显式引用，不能靠临时表或变量中转。

常见错误是把 INSERT INTO audit_log ... SELECT ... FROM OLD 写成 INSERT INTO audit_log VALUES (OLD.id, OLD.name)，结果 MySQL 报错 Unknown column 'OLD.id' in 'field list'。

MySQL 必须用 OLD.column_name 直接写在 VALUES 里，如：INSERT INTO audit_log (table_name, op_type, old_value, new_value) VALUES ('users', 'UPDATE', OLD.email, NEW.email);
PostgreSQL 可用 OLD.* 展开，但字段顺序要和目标表严格一致；更稳妥的是显式列出：INSERT INTO audit_log VALUES (TG_TABLE_NAME, 'UPDATE', OLD.id, OLD.email, NEW.email);
SQL Server 不支持 OLD/NEW，得用 deleted 和 inserted 临时表，且必须用 JOIN 关联主键，否则多行更新会爆炸

审计日志表设计不带事务上下文，导致无法追溯业务操作？

只存 op_type、table_name、record_id 和字段快照，看起来够用，但线上排查时发现：同一秒内多个用户改同一条订单，日志里全是 UPDATE order SET status=2，根本分不清谁点的“发货”、谁点的“取消”。

关键缺失是业务维度标识。不是所有系统都用 current_user()，比如连接池复用下它返回数据库用户而非应用用户。

务必在应用层通过 SET LOCAL app.user_id = 'u1001'（PostgreSQL）或 SET @app_user_id = 'u1001'（MySQL）透传身份，触发器里用 current_setting('app.user_id', true) 或 @app_user_id 读取
加 trace_id 字段，应用在事务开始时生成唯一 ID 并 SET 进去，审计日志按 trace_id 聚合就能还原完整操作链
避免在触发器里调用 NOW() 多次——不同行可能差几毫秒，应统一用 TG_OP 触发时刻的 CURRENT_TIMESTAMP（PostgreSQL）或 NOW(3)（MySQL）

触发器执行慢，拖垮主表 INSERT/UPDATE 性能？

审计日志表如果建了太多索引（比如给每个字段都加单列索引），或者用了 TEXT 类型存 JSON 快照却没加 GENERATED COLUMN + INDEX，一次 INSERT 审计可能耗时 20ms+，而主业务逻辑才 5ms。

性能瓶颈往往不在 SQL 本身，而在锁和 WAL 日志膨胀。

MySQL 下审计表用 innodb_buffer_pool_size 占比别超 10%，否则刷脏页会卡住主表 DML
PostgreSQL 必须关掉审计表的 autovacuum（设为 false），改用后台作业按天分区 + VACUUM，否则高并发写入时 autovacuum worker 会抢锁
禁止在触发器里做跨库写入、HTTP 调用或复杂 JSON 解析——这些应该由 CDC 工具（如 Debezium）或应用层异步投递完成

DELETE 操作后审计日志查不到原始数据？

AFTER DELETE 触发器里 OLD 可用，但若审计表本身被误删、归档策略激进，或应用用了 TRUNCATE（它不走触发器），日志就彻底丢失。

最隐蔽的坑是外键级联删除：比如 orders 表 ON DELETE CASCADE 删除 order_items，触发器只在 order_items 上定义，但 orders 主记录的删除动作完全没留痕。

TRUNCATE 无解，只能靠权限控制 + 审计数据库登录行为（如 pgAudit 插件）
级联操作必须在父表也建触发器，并在日志里加 cascade_from 字段标记来源，例如：INSERT INTO audit_log (cascade_from) VALUES ('orders.id=123')
对敏感 DELETE，建议改用软删（is_deleted 标记），硬删仅限归档脚本执行，且脚本自身要写独立审计日志

真正难的不是写触发器，而是让每条日志能回答三个问题：谁在什么上下文里、基于什么旧状态、做了什么变更。少一个维度，排查时就要翻三倍日志。