必须手动逐字段比对OLD/NEW或deleted/inserted,用IS NOT DISTINCT FROM(PostgreSQL)或COALESCE安全处理NULL,仅比关键业务字段,通过JSON_OBJECT/jsonb_build_object等生成合法JSON差量,避免触发器内耗时操作。

触发器里怎么拿到更新前后的字段差异
SQL 触发器本身不直接提供「变更字段列表」或「旧值/新值对比结构」,必须手动用 OLD.* 和 NEW.*(MySQL/PostgreSQL)或 deleted.*/inserted.*(SQL Server)逐字段比对。别指望自动 diff —— 这是绝大多数人卡住的第一步。
常见错误现象:NEW.column_name = OLD.column_name 判断相等时,NULL 值导致逻辑失效(因为 NULL = NULL 返回 UNKNOWN)。必须显式用 IS NOT DISTINCT FROM(PostgreSQL)或 COALESCE(NEW.col, '§NULL§') != COALESCE(OLD.col, '§NULL§')(MySQL/SQL Server)来安全比较。
实操建议:
- 只比对业务上关心的字段,避免全表字段硬编码(比如跳过
updated_at、version等非业务字段) - 把字段名、旧值、新值组装成键值对临时结构(如 PostgreSQL 的
jsonb_build_object()或 MySQL 8.0+ 的JSON_OBJECT()) - 用
CASE WHEN+ 字符串拼接兜底兼容老版本 MySQL(5.7),但 JSON 格式易出错,不推荐
怎么把差量数据序列化成标准 JSON 对象
目标不是拼字符串,而是生成合法、可解析、带类型信息的 JSON。各数据库 JSON 函数能力差异大,不能混用。
PostgreSQL 最省心:jsonb_build_object('field', OLD.field, 'old', OLD.field, 'new', NEW.field) 可嵌套,支持 jsonb_agg() 聚合成数组;MySQL 5.7 起有 JSON_OBJECT(),但不支持动态键名,得靠 JSON_SET() 逐个注入;SQL Server 2016+ 用 FOR JSON PATH,但只能从子查询来,不能直接用 deleted/inserted 表字段构造。
实操建议:
- MySQL:用
JSON_OBJECT('field', 'name', 'old', OLD.name, 'new', NEW.name)构造单条变更项,再用JSON_ARRAY_APPEND(@diff, '$', …)累加(注意初始化@diff := '[]') - PostgreSQL:直接
jsonb_agg(jsonb_build_object('field', k, 'old', OLD.k, 'new', NEW.k)) FILTER (WHERE ...),配合json_each_text()动态遍历字段(需提前定义字段数组) - SQL Server:写 CTE 查询
deleted和inserted的 UNION ALL,加标识列区分 old/new,再用FOR JSON AUTO或手工STRING_AGG拼键值对
触发器里存 JSON 到历史表要注意什么
别在触发器里做耗时操作。把 JSON 写进另一张 audit_log 表看似简单,但容易引发锁竞争或事务膨胀 —— 尤其高并发 UPDATE 场景下,主表和日志表的行锁可能互相阻塞。
一个基于官方 Mailtrap Node.js SDK 构建的 ASE 技能,用于发送事务邮件并与 Mailtrap 沙盒或生产流程协作。它非常适合需要 API 驱动邮件投递、测试邮箱验证和结构化消息自动化的代理工作流。
常见错误现象:INSERT INTO audit_log (...) VALUES (...) 直接写入,导致主事务变慢、超时,甚至死锁;或 JSON 字段定义为 TEXT 却没设足够长度(MySQL 默认 TEXT 最大 64KB,差量大时溢出报错 ERROR 1406: Data too long)。
实操建议:
- JSON 字段用
JSON类型(MySQL/PostgreSQL)或NVARCHAR(MAX)(SQL Server),避免类型隐式转换失败 - 限制单次记录的差量字段数(比如最多 20 个),超出则截断并加标记字段
"truncated": true - 考虑异步方案:触发器只写轻量消息到队列表(含主键、操作类型、时间戳),由后台任务拉取并生成完整 JSON,降低主流程压力
跨数据库移植时最常崩在哪几个点
字段名大小写、NULL 处理、JSON 函数语法、触发器执行时机(BEFORE/AFTER)—— 四个点只要一个不一致,整个逻辑就挂。
例如 PostgreSQL 的 NEW.field 是大小写敏感的(除非双引号包裹),而 MySQL 默认忽略大小写;SQL Server 的 inserted 表在 AFTER UPDATE 中才可见,BEFORE 不支持;MySQL 8.0 才支持 JSON_CONTAINS_PATH(),老版本得用正则匹配 JSON 字符串,极其脆弱。
实操建议:
- 所有字段引用统一小写,建表时避免双引号(PostgreSQL)或反引号(MySQL)包裹
- 把差量生成逻辑封装成数据库函数(如
fn_diff_to_json(OLD, NEW)),不同库各自实现,触发器只调用接口 - 测试必须覆盖 NULL 更新(如从
'abc'→NULL)、空字符串更新(''→' ')、长文本截断等边界情况
真正难的不是写 JSON,是让 diff 结果既准确又稳定,还能扛住不同环境的数据毛刺和类型抖动。字段多、NULL 多、数据库版本杂的时候,手动比对那几行 IF 逻辑,比写业务代码还容易漏 case。










