MySQL如何简化多字段变更日志的AFTER UPDATE触发器编写

MySQL触发器原生不支持动态遍历表字段自动识别变更，不需要沿用逐字段写IF+单独INSERT的冗余写法，使用单INSERT语句+UNION ALL多分支行构造的方式即可实现需求，同时大幅减少重复代码。

优化后触发器代码 #

DELIMITER $$
DROP TRIGGER IF EXISTS `user_log`$$
CREATE TRIGGER `user_log`
AFTER UPDATE ON `user` 
FOR EACH ROW
BEGIN
  INSERT INTO user_log (user_id, action, old_data, new_data)
  -- 姓名变更日志
  SELECT NEW.user_id, CONCAT('Changed ', OLD.name, '''s name'), OLD.name, NEW.name
  WHERE OLD.name <> NEW.name OR (OLD.name IS NULL) <> (NEW.name IS NULL)
  UNION ALL
  -- 地址变更日志
  SELECT NEW.user_id, CONCAT('Changed ', OLD.name, '''s address'), OLD.address, NEW.address
  WHERE OLD.address <> NEW.address OR (OLD.address IS NULL) <> (NEW.address IS NULL)
  UNION ALL
  -- 城市变更日志
  SELECT NEW.user_id, CONCAT('Changed ', OLD.name, '''s city'), OLD.city, NEW.city
  WHERE OLD.city <> NEW.city OR (OLD.city IS NULL) <> (NEW.city IS NULL)
  UNION ALL
  -- 手机号变更日志
  SELECT NEW.user_id, CONCAT('Changed ', OLD.name, '''s phone number'), OLD.phone, NEW.phone
  WHERE OLD.phone <> NEW.phone OR (OLD.phone IS NULL) <> (NEW.phone IS NULL);
  -- 后续新增监控字段只需按上述格式追加UNION ALL块即可
END$$
DELIMITER ;

方案说明 #

• 每个UNION ALL块对应一个待监控字段，只有字段实际发生变更时，对应的SELECT子句才会返回结果行，最终一次性插入所有变更字段的独立日志，和原逐IF判断插入的效果完全一致
• 去掉了原写法中冗余的CASE判断和重复的INSERT语句结构，新增监控字段时仅需复制已有UNION ALL块修改字段名、字段描述即可，代码量减少70%以上
• 条件中增加了NULL值判断，避免字段允许为NULL时，<>比较返回UNKNOWN导致的日志漏记问题
• 执行效率和原写法无差异，每个字段的变更判断逻辑独立，不会额外增加查询开销

超大量字段的快速生成方式 #

如果user表字段多达几十个，手动写UNION ALL块依然麻烦，可以直接查询系统表information_schema.COLUMNS自动生成完整触发器代码，执行后直接复制结果使用即可：

-- 执行前替换表名、排除不需要监控的字段规则
SELECT CONCAT(
  'DELIMITER $$\nDROP TRIGGER IF EXISTS `user_log`$$\nCREATE TRIGGER `user_log`\nAFTER UPDATE ON `user` \nFOR EACH ROW\nBEGIN\n  INSERT INTO user_log (user_id, action, old_data, new_data)\n',
  GROUP_CONCAT(
    CONCAT(
      '  SELECT NEW.user_id, CONCAT(''Changed '', OLD.name, ''''s ', REPLACE(COLUMN_NAME,'_',' '), '''), OLD.', COLUMN_NAME, ', NEW.', COLUMN_NAME, '\n  WHERE OLD.', COLUMN_NAME, ' <> NEW.', COLUMN_NAME, ' OR (OLD.', COLUMN_NAME, ' IS NULL) <> (NEW.', COLUMN_NAME, ' IS NULL)'
    ) SEPARATOR '\n  UNION ALL\n'
  ),
  ';\nEND$$\nDELIMITER ;'
) AS trigger_generate_code
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'user'
  -- 排除不需要记录变更的字段，比如主键、创建时间、更新时间等
  AND COLUMN_NAME NOT IN ('user_id', 'create_time', 'update_time');

你之前尝试的单VALUES+多CASE写法无法满足需求的核心原因是：CASE语句按顺序匹配，命中第一个符合条件的分支就会返回值，单次INSERT只能插入一条记录，自然无法记录多字段同时变更的场景，UNION ALL的写法刚好规避了这个问题，每个变更字段对应独立的结果行。

内容的提问来源于stack exchange，提问作者ugsgknt