怎样在SQL触发器中将单条更新拆分为详细审计记录？-SQL-PHP中文网

MySQL BEFORE UPDATE触发器用OLD.字段名和NEW.字段名获取更新前后值，PostgreSQL通过OLD/NEW记录变量访问，SQL Server则需用INSERTED/DELETED临时表并关联主键；均须显式处理NULL比较。

怎样在sql触发器中将单条更新拆分为详细审计记录？

触发器里怎么拿到更新前后的字段值

MySQL 的 BEFORE UPDATE 触发器能用 OLD.字段名 和 NEW.字段名 分别读取旧值和新值，PostgreSQL 则通过 OLD 和 NEW 记录变量访问。SQL Server 要用 INSERTED 和 DELETED 临时表——注意它没有单行上下文，哪怕只改一条记录，这两张表也可能是多行，得加 WHERE 关联主键。

常见错误是直接在 AFTER UPDATE 里查原表想“还原旧值”，结果查到的已是新数据；或者在 SQL Server 中忽略 INSERTED 和 DELETED 行数不一致，导致审计记录漏字段或重复。

MySQL/PG：确保触发器类型为 BEFORE UPDATE，否则 OLD 在 AFTER 里不可修改（但可读）
SQL Server：必须用主键或唯一约束字段做 JOIN，例如 ON INSERTED.id = DELETED.id
字段类型要兼容：比如 NEW.phone 是 VARCHAR，审计表对应列不能是 INT，否则插入失败

怎么只记录真正变化的字段，而不是全量写入

硬写 IF NEW.name != OLD.name THEN INSERT ... 看似直观，但 NULL 处理会翻车：NULL != NULL 返回 UNKNOWN，条件不成立。MySQL 要用 IS NULL 显式判断，PostgreSQL 可用 IS DISTINCT FROM，SQL Server 得写 (NEW.col OLD.col) OR (NEW.col IS NULL AND OLD.col IS NOT NULL) OR (NEW.col IS NOT NULL AND OLD.col IS NULL)。

更稳妥的做法是把比较逻辑封装成函数（如 PostgreSQL 的 is_changed()），或用 JSON 差异比对（适合字段多、变更少的场景）。

避免在触发器里拼接长字符串做日志，容易超长截断；优先用结构化字段存 old_value / new_value
时间戳统一用 NOW() 或 CURRENT_TIMESTAMP，别依赖应用层传入，防止时钟不一致
敏感字段（如密码、身份证）不要进审计表，触发器里直接 SKIP 或写固定掩码值

审计表设计不当引发的性能崩塌

如果审计表没建索引，按用户ID查某人所有操作历史会全表扫描；如果用 TEXT 存 JSON 格式变更详情，又没加生成列+索引，后续按字段值检索就只能 LIKE 或全量解析。

典型反模式：审计表主键用自增 ID，但业务查询永远带 table_name + record_id + updated_at 三条件——结果每次都要扫几十万行。

必建复合索引：INDEX idx_audit_target (table_name, record_id, updated_at)
变更详情字段若需搜索，MySQL 8.0+ 可建虚拟列 + 索引，如 old_email VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(change_data, '$.email.old')))
审计表定期归档，别让它和业务表一样大；可用分区表按月切分，或用事件自动 DELETE 超过180天的数据

跨数据库迁移时最常被忽略的细节

MySQL 触发器里能直接写 INSERT INTO audit_log VALUES (OLD.id, 'user', 'status', OLD.status, NEW.status, NOW());，但 PostgreSQL 要求明确列出字段名，SQL Server 则不允许在触发器里调用含事务的存储过程（除非设 XACT_ABORT OFF）。

还有字符集问题：MySQL 审计表用 utf8mb4，但业务表是 latin1，OLD.name 插进来可能乱码；SQL Server 若原字段是 NVARCHAR，审计表对应列没加 N 前缀，中文就变问号。