MySQL和PostgreSQL均通过OLD.字段名、NEW.字段名显式访问变更值,不可动态拼接;字段名须严格匹配表定义(大小写、引号规则各异),NULL比较需用IS NOT DISTINCT FROM(PG)或IFNULL(MySQL)避免误判。

触发器里怎么拿到 OLD 和 NEW 的字段值?
MySQL 和 PostgreSQL 都支持在 BEFORE UPDATE 或 AFTER UPDATE 触发器中通过 OLD.字段名 和 NEW.字段名 访问变更前后的值,但**不能动态拼接字段名**(比如 OLD.'col_' || i 这种写法在标准 SQL 触发器里不合法)。这意味着你无法用一个通用循环遍历所有列——必须显式写出要监控的字段。
常见错误现象:Unknown column 'OLD.col_name' in 'field list',往往是因为误写了引号(OLD.'col' 错),或字段名大小写/拼写与表定义不一致。
- MySQL 区分大小写取决于系统变量
lower_case_table_names和列定义,建议统一用小写+反引号包裹:OLD.`user_name` - PostgreSQL 默认转小写,字段名含大写需双引号:
OLD."UserName" - 如果字段允许 NULL,比较时别用
=,改用IS NOT DISTINCT FROM(PostgreSQL)或IFNULL(OLD.x, '') = IFNULL(NEW.x, '')(MySQL)避免 NULL 导致判断失效
如何只记录真正变动的字段,而不是每行都插一条日志?
直接在触发器里对每个字段写 IF OLD.x != NEW.x THEN INSERT ... 是可行的,但字段一多就容易漏判、难维护。关键是把“变动检测”和“日志插入”解耦,用临时结构暂存差异。
MySQL 示例(使用局部变量 + 多个 IF):
IF OLD.status != NEW.status THEN
INSERT INTO audit_log (table_name, row_id, field_name, old_value, new_value, updated_at)
VALUES ('orders', NEW.id, 'status', OLD.status, NEW.status, NOW());
END IF;
PostgreSQL 更简洁,可用 ROW() 比较整行,但字段级仍需展开;若字段超 10 个,建议用 jsonb_each() 配合 to_jsonb(OLD) 和 to_jsonb(NEW) 做键值比对(仅限 PG 9.5+):
SELECT k, v AS old_val, n.v AS new_val FROM jsonb_each(to_jsonb(OLD)) AS t(k,v) JOIN jsonb_each(to_jsonb(NEW)) AS n(k,v) USING(k) WHERE v != n.v;
INSERT/DELETE 场景下怎么复用同一套变动记录逻辑?
INSERT 没有 OLD,DELETE 没有 NEW,所以不能简单套用 UPDATE 的条件分支。必须按事件类型分三段处理,且字段值来源不同:
-
INSERT:所有字段来自NEW,old_value字段填NULL或固定字符串如'[INSERT]' -
DELETE:所有字段来自OLD,new_value填NULL或'[DELETE]' -
UPDATE:按字段逐个比对,仅插入变动项
容易踩的坑:INSERT 触发器里引用 OLD.id 会报错 Reference 'OLD' not supported in trigger;同理,DELETE 里不能用 NEW。触发器体开头先用 IF TG_OP = 'UPDATE' THEN ... ELSIF TG_OP = 'INSERT' THEN ... 明确分路。
audit_log 表设计要注意哪些性能和查询陷阱?
字段级变动日志表如果没规划好,很快会变成查询黑洞。最常被忽略的是索引和字段类型:
-
old_value/new_value别全用TEXT——短字段(如状态码、布尔)用VARCHAR(64)更快,也方便加前缀索引 - 必须给
(table_name, row_id)加联合索引,否则查某条订单的所有变更要全表扫 - 如果常用“查某字段最近 10 次变更”,给
(field_name, updated_at)加索引,但注意 MySQL 不支持函数索引,updated_at DESC要显式声明 - PostgreSQL 可考虑用
GENERATED ALWAYS AS (...) STORED自动存变更摘要(如md5(old_value || new_value)),避免大字段重复存储
真正麻烦的不是写触发器,而是当表有 30 个字段、每天更新 10 万次时,单次 UPDATE 触发 30 次 INSERT 到 audit_log —— 这种量级下,异步队列或应用层拦截反而更可控。触发器适合中小规模、强一致性要求的场景,别把它当万能日志开关。

















