在业务系统运行过程中，数据表的字段值往往会随着业务操作发生变更，为了后续能够追溯数据的变化过程、排查异常问题或者满足审计合规要求，我们需要设计合理的SQL字段变化追踪策略，实现历史值的可靠保存。不同的业务场景对历史数据的要求不同，适用的方案也存在差异，下面介绍几种主流的实现方式。
基于触发器的历史值保存方案
触发器是数据库内置的功能，能够在数据发生插入、更新、删除操作时自动执行预设的逻辑，非常适合用来实现字段变化追踪。我们可以为需要监控的业务表创建对应的审计表，当有数据变更时，触发器自动将变更前后的字段值、操作时间、操作人等信息写入审计表。
首先创建业务表user_info，用于存储用户基本信息：
-- 创建用户业务表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
phone VARCHAR(20),
email VARCHAR(100),
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
接下来创建对应的审计表user_info_audit，用于保存字段变化的历史记录：
-- 创建用户表审计表
CREATE TABLE user_info_audit (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
old_username VARCHAR(50),
new_username VARCHAR(50),
old_phone VARCHAR(20),
new_phone VARCHAR(20),
old_email VARCHAR(100),
new_email VARCHAR(100),
operate_type VARCHAR(10) NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE',
operate_time DATETIME NOT NULL,
operate_user VARCHAR(50)
);
然后为user_info表创建更新触发器，当有用户数据更新时自动记录变更信息：
-- 创建更新触发器
DELIMITER //
CREATE TRIGGER trg_user_info_update
AFTER UPDATE ON user_info
FOR EACH ROW
BEGIN
INSERT INTO user_info_audit (
user_id,
old_username,
new_username,
old_phone,
new_phone,
old_email,
new_email,
operate_type,
operate_time,
operate_user
) VALUES (
OLD.id,
OLD.username,
NEW.username,
OLD.phone,
NEW.phone,
OLD.email,
NEW.email,
'UPDATE',
NOW(),
CURRENT_USER()
);
END //
DELIMITER ;
如果需要同时追踪插入和删除操作，可以再创建对应的插入触发器和删除触发器，逻辑和更新触发器类似，只是操作类型和字段取值有所区别。
方案优缺点
- 优点：无需修改应用层代码，数据库层面自动完成历史记录保存，可靠性高，不会因为应用层逻辑漏洞导致历史数据丢失。
- 缺点：会增加数据库的性能开销，尤其是高频更新的表，触发器执行会延长事务的执行时间；不同数据库的触发器语法存在差异，迁移成本较高。
应用层主动记录历史值方案
这种方案不需要依赖数据库触发器，而是在应用层执行数据更新操作之前，先查询当前记录的字段值，再将旧值、新值以及操作信息写入审计表。这种方式的控制权在应用层，灵活性更高。
以Java应用为例，更新用户信息时记录历史值的逻辑如下：
// 更新用户信息并记录历史值
public void updateUser(UserInfo newUser, String operateUser) {
// 1. 查询当前用户的旧数据
UserInfo oldUser = userDao.selectById(newUser.getId());
// 2. 执行更新操作
userDao.update(newUser);
// 3. 构建审计记录并保存
UserAudit audit = new UserAudit();
audit.setUserId(newUser.getId());
audit.setOldUsername(oldUser.getUsername());
audit.setNewUsername(newUser.getUsername());
audit.setOldPhone(oldUser.getPhone());
audit.setNewPhone(newUser.getPhone());
audit.setOldEmail(oldUser.getEmail());
audit.setNewEmail(newUser.getEmail());
audit.setOperateType("UPDATE");
audit.setOperateTime(new Date());
audit.setOperateUser(operateUser);
userAuditDao.insert(audit);
}
方案优缺点
- 优点：不依赖数据库特性，跨数据库兼容性好；可以根据业务需求灵活控制是否需要记录历史，也可以自定义记录的内容，适配复杂的业务规则。
- 缺点：需要在所有涉及数据变更的业务逻辑中添加记录历史的代码，容易遗漏；如果应用层出现bug，可能导致历史记录没有正常保存。
基于数据库日志的字段变化追踪方案
主流的关系型数据库都会记录事务日志，比如MySQL的binlog、PostgreSQL的WAL日志，这些日志中包含了所有数据变更的详细信息。我们可以通过解析数据库的日志文件，提取出字段变化的相关信息，再保存到历史表中。
这种方式通常需要配合第三方工具使用，比如MySQL可以使用Canal中间件解析binlog，PostgreSQL可以使用wal2json插件解析WAL日志。以Canal解析MySQL binlog为例，整体流程如下：
- 配置MySQL开启binlog，并且设置为ROW模式，确保日志中包含每行数据的具体变更内容。
- 部署Canal服务，配置监听对应的数据库和表。
- Canal解析到binlog变更事件后，推送给应用层，应用层根据事件内容提取字段变化信息，写入审计表。
方案优缺点
- 优点：对业务表和应用程序完全没有侵入性，不会影响原有业务的性能；可以获取到所有数据变更的记录，包括直接通过数据库客户端执行的变更操作。
- 缺点：部署和配置复杂度高，需要额外维护中间件；解析日志的逻辑相对复杂，需要处理不同表结构变更的场景。
不同方案的选择建议
我们可以根据实际业务场景选择合适的SQL字段变化追踪策略：
| 方案类型 | 适用场景 | 核心优势 |
|---|---|---|
| 触发器方案 | 对历史数据完整性要求高、业务表变更频率不高、数据库类型固定的场景 | 实现简单，无需修改应用代码 |
| 应用层方案 | 需要灵活控制历史记录逻辑、跨数据库兼容的场景 | 灵活性高，适配复杂业务规则 |
| 日志解析方案 | 需要追踪所有数据变更、对业务无侵入要求的场景 | 无侵入，覆盖所有变更来源 |
在实际落地时，也可以结合多种方案使用，比如核心业务表使用触发器保证历史数据不丢失，非核心表使用应用层方案降低复杂度，从而平衡开发成本、性能和数据可靠性。