线上系统跑久了，总会遇到这样的情况：某天业务方反馈一笔订单数据不对劲，金额被人改过，但没人承认；或者某个字段被定时任务意外批量刷新，原值彻底丢失。这时候如果有备库也许能翻到部分历史，但如果变更发生多次，想精确还原某一时刻的状态几乎不可能。解决这个问题的思路其实很简单，就是在数据发生变更的那一刻，把变更的内容自动抄写一份到另一张表里。MySQL的触发器机制正好能胜任这件事，本文就来完整讲解如何用触发器加影子表的方式，为你的核心表建立一套数据变更留痕方案。
一、先弄清楚触发器的工作原理
触发器是绑定在某个表上的数据库对象，它指定了一个触发时机（BEFORE或AFTER）和一个触发事件（INSERT、UPDATE、DELETE）。当对应事件发生时，MySQL会自动执行触发器体内编写的SQL逻辑。要做数据备份，我们通常选择AFTER触发器，因为AFTER阶段新数据已经写入成功，此时记录下来的内容才是真实落库的值。
在触发器体内，MySQL提供了两张特殊的只读表供我们访问：NEW和OLD。对于INSERT操作，只有NEW可用，代表即将插入或刚插入的新行；对于UPDATE操作，NEW和OLD都可用，OLD代表修改前的旧值，NEW代表修改后的新值。这个特性对我们做变更记录非常关键：把OLD写进日志就是变更前的快照，把NEW写进去就是变更后的快照。
需要注意触发器的几个限制：触发器体内不能使用动态SQL，不能调用返回结果集给客户端的语句，也不能修改触发它的那张表本身，否则会报错。另外，触发器是跟随语句级触发的，一条UPDATE影响一百行，触发器就会执行一百次，这一点在性能评估时必须考虑进去。
二、创建影子表并编写触发器
所谓影子表，就是一张结构上参考主表、但额外增加了审计字段的备份表。假设我们有一张订单表，结构如下：
CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
为它创建影子表时，建议采用“一行记录一次变更”的设计：把主表的所有业务字段原样复制一份，再追加操作类型、操作时间和回滚用的主键。为了能存下UPDATE前后的两份值，可以在字段名前加old_和new_前缀来区分：
CREATE TABLE orders_audit (
audit_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
operation ENUM('INSERT','UPDATE') NOT NULL,
row_id BIGINT UNSIGNED NOT NULL,
old_order_no VARCHAR(32) DEFAULT NULL,
new_order_no VARCHAR(32) DEFAULT NULL,
old_amount DECIMAL(12,2) DEFAULT NULL,
new_amount DECIMAL(12,2) DEFAULT NULL,
old_status TINYINT DEFAULT NULL,
new_status TINYINT DEFAULT NULL,
operator VARCHAR(64) DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
接着编写两个触发器。AFTER INSERT触发器负责记录新增的行，此时OLD不存在，只写new_开头的字段；AFTER UPDATE触发器则把新旧两份值都记下来：
DELIMITER $$
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (operation, row_id,
new_order_no, new_amount, new_status)
VALUES ('INSERT', NEW.id,
NEW.order_no, NEW.amount, NEW.status);
END$$
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_audit (operation, row_id,
old_order_no, old_amount, old_status,
new_order_no, new_amount, new_status)
VALUES ('UPDATE', OLD.id,
OLD.order_no, OLD.amount, OLD.status,
NEW.order_no, NEW.amount, NEW.status);
END$$
DELIMITER ;
创建完成后可以做个简单验证：先插入一条订单，再把金额从100改成250，然后查询审计表，你会看到两条记录，一条INSERT日志带着初始值，一条UPDATE日志同时保留了100和250两份金额。日后如果发现数据异常，顺着审计表的时间线就能完整还原每一次变更。
三、记录操作人与常见坑点应对
审计日志如果不知道是谁改的，价值会大打折扣。但触发器本身无法直接获取应用层的用户身份，常用的做法是借助会话变量：应用在建立连接后执行一句SET @current_user = 'zhangsan'，触发器体内通过@current_user读取即可。也可以使用MySQL内置的USER()函数记录数据库账号，适合区分不同服务使用不同账号的场景。
-- 在UPDATE触发器中补充操作人字段
INSERT INTO orders_audit (operation, row_id,
old_amount, new_amount, operator)
VALUES ('UPDATE', OLD.id,
OLD.amount, NEW.amount,
IFNULL(@current_user, USER()));
第一个坑是性能。前面提到触发器按行触发，大事务批量更新百万行时，会额外产生百万次审计插入。建议把审计表与主表放在同一个实例上以减少网络开销，并定期归档历史数据。如果业务存在超大批量更新，可以考虑只在触发器内判断关键字段是否变化，未变化的行不写日志：
IF NOT (OLD.amount <=> NEW.amount AND OLD.status <=> NEW.status) THEN -- 只有真正变化才写审计记录 INSERT INTO orders_audit (...) VALUES (...); END IF;
第二个坑是影子表结构漂移。主表以后加字段时，影子表和触发器都要同步修改，否则触发器会因字段不存在而报错，连带主表写入失败。管理规范一点的团队会把建表、建触发器的SQL放进版本库，主表变更时一并提交影子表的变更脚本，避免遗漏。也可以写一个定时任务比对information_schema.COLUMNS中两张表的结构差异，发现不一致立即告警。
第三个坑是日志表膨胀。审计表只增不减，跑上一年数据量可能远超主表本身。可以按月建分区表，或者定期把超过保留期的数据归档到历史库。查询时尽量带上row_id和created_at条件，并为其建联合索引，避免审计查询拖垮整个库。
最后要说明的是，触发器方案适合中小数据量、对实时性要求高的审计场景。如果表的数据规模特别大，或者需要更丰富的分析能力，可以考虑Canal、Debezium这类基于binlog的 CDC 工具，它们对业务零侵入，代价是部署和运维复杂度更高。两种方案也可以并存：触发器保底记录核心表，CDC做全量采集，具体取舍看业务的重要程度和团队的技术储备。