如何实现审计日志仅存储更新列而非全表所有列数据

这种需求不要设计成单条审计记录存整行新旧值的宽表结构，用窄表按变更列逐行存储的方式最符合要求，既没有冗余数据，排查变更的时候也不用逐列比对找差异。

审计表结构设计 #

表结构完全匹配需求，只存必要字段，单条审计记录对应单个列的一次值变更：

• audit_id：bigint类型自增主键，唯一标识单条审计记录
• biz_record_id：和业务表主键类型保持一致，关联被修改的业务数据行
• changed_column：varchar类型，存储实际发生值变更的字段名
• old_val：text类型，存储字段更新前的原始值，统一转字符串存储兼容不同字段类型
• new_val：text类型，存储字段更新后的新值
• update_at：timestamp类型，记录更新操作的时间戳，默认取数据库当前时间即可

以MySQL为例的建表语句：

CREATE TABLE `biz_data_audit_log` (
  `audit_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `biz_record_id` bigint NOT NULL COMMENT '业务表主键值',
  `changed_column` varchar(64) NOT NULL COMMENT '发生变更的列名',
  `old_val` text COMMENT '更新前旧值',
  `new_val` text COMMENT '更新后新值',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`audit_id`),
  KEY `idx_biz_record` (`biz_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更新触发逻辑实现 #

用数据库触发器实现是最稳妥的方式，不用依赖业务代码逻辑，核心规则是逐列对比更新前后的值，只有值实际发生变化的列才生成审计记录，单次更新多列就生成多条对应列的审计记录，不会存储无变更列的冗余数据。

以MySQL为例，假设业务表名为biz_table，主键为id，10个业务列分别命名为col1到col10，触发器写法如下：

DELIMITER //
CREATE TRIGGER `trg_biz_table_audit_update` AFTER UPDATE ON `biz_table`
FOR EACH ROW
BEGIN
  -- 逐列判断值是否变更，注意兼容NULL值判断场景
  IF OLD.col1 <> NEW.col1 OR (OLD.col1 IS NULL) <> (NEW.col1 IS NULL) THEN
    INSERT INTO `biz_data_audit_log`(`biz_record_id`, `changed_column`, `old_val`, `new_val`)
    VALUES (OLD.id, 'col1', OLD.col1, NEW.col1);
  END IF;

  IF OLD.col2 <> NEW.col2 OR (OLD.col2 IS NULL) <> (NEW.col2 IS NULL) THEN
    INSERT INTO `biz_data_audit_log`(`biz_record_id`, `changed_column`, `old_val`, `new_val`)
    VALUES (OLD.id, 'col2', OLD.col2, NEW.col2);
  END IF;

  -- col3到col10按照上述相同判断逻辑逐行编写即可
  -- 注意不要遗漏NULL值判断，否则字段在NULL和非NULL值之间切换时会漏记
END //
DELIMITER ;

注意事项 #

• 如果使用PostgreSQL、SQL Server等其他数据库，触发器语法有差异，但核心逻辑不变：拿到更新前后的行数据，逐列比对差异，仅为实际变更的列插入审计记录
• 如果需要追溯操作人，可以在审计表加operator字段，业务层更新数据时把当前操作人ID写入业务表的预留更新人字段，触发器里直接取该值存入审计表即可
• 这种结构查询变更非常方便：要查某条业务数据的所有变更，直接按biz_record_id过滤即可，每条记录清晰对应哪个列在什么时间从什么值改成了什么值，不需要额外做全列比对

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