4. 基于触发器的CDC实现与校验

触发器（Trigger）是数据库里最古老、也最直接的CDC手段。说白了，就是在源表上挂个钩子——数据一变，触发器自动把变更记录写到另一张表里。我早年做银行核心系统迁移时，就靠这招撑过了整整两年的数据一致性审计。今天咱们就把它掰开揉碎了讲清楚。

4.1 审计表的设计与创建

审计表是触发器的“仓库”。设计得好，后面查问题就省心；设计得烂，查个历史记录能把你逼疯。

我个人习惯把审计表分成两类：通用型和业务型。通用型记录所有字段的变更，适合做全量审计；业务型只记录关键字段，适合性能敏感的场景。

先看一个通用型审计表的DDL：

CREATE TABLE orders_audit (
    audit_id        BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name      VARCHAR(64) NOT NULL COMMENT '源表名',
    operation_type  ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    row_id          BIGINT NOT NULL COMMENT '源表主键',
    old_data        JSON COMMENT '变更前的整行数据',
    new_data        JSON COMMENT '变更后的整行数据',
    changed_fields  JSON COMMENT '仅变更的字段名列表',
    operator        VARCHAR(64) COMMENT '操作人/应用名',
    client_ip       VARCHAR(45) COMMENT '客户端IP',
    change_time     DATETIME(3) NOT NULL COMMENT '变更时间戳(毫秒精度)',
    batch_id        BIGINT DEFAULT 0 COMMENT '批量任务ID,用于下游消费',
    INDEX idx_change_time (change_time),
    INDEX idx_row_id (row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里有几个设计要点：

  • 用JSON存old_data和new_data：字段多了不愁，加字段也不用改审计表结构。我在项目中遇到过业务表一年改了三次字段，要是用固定列存，每次都得跑ALTER TABLE，太痛苦了。
  • 加batch_id字段：下游消费时用来做幂等去重。没有这个字段，数据重复了你都不知道是触发器写了两遍还是消费端重试了。
  • 时间戳精度到毫秒：高并发场景下，秒级精度根本不够用。同一秒内两条记录谁先谁后？说不清楚。
小技巧：如果源表数据量特别大（比如上亿行），建议给审计表做按月分区。我习惯用change_time做RANGE分区，这样清理历史数据时直接DROP分区，比DELETE快两个数量级。

4.2 触发器逻辑编写

触发器逻辑的核心就三件事：捕获变更、组装数据、写入审计表。听起来简单，但坑不少。

先看一个INSERT触发器的例子：

DELIMITER $$

CREATE TRIGGER trg_orders_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (
        table_name, operation_type, row_id,
        old_data, new_data, changed_fields,
        operator, client_ip, change_time
    ) VALUES (
        'orders',
        'INSERT',
        NEW.id,
        NULL,
        JSON_OBJECT(
            'id', NEW.id,
            'customer_id', NEW.customer_id,
            'amount', NEW.amount,
            'status', NEW.status,
            'created_at', NEW.created_at
        ),
        JSON_ARRAY('id','customer_id','amount','status','created_at'),
        COALESCE(@current_operator, USER()),
        COALESCE(@current_client_ip, SUBSTRING_INDEX(USER(),'@',-1)),
        NOW(3)
    );
END$$

DELIMITER ;

UPDATE触发器稍微复杂一点，因为要同时记录旧值和新值：

DELIMITER $$

CREATE TRIGGER trg_orders_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    DECLARE changed JSON;

    -- 动态计算哪些字段变了
    SET changed = JSON_ARRAY();
    IF OLD.customer_id <=> NEW.customer_id THEN
        SET changed = JSON_ARRAY_APPEND(changed, '$', 'customer_id');
    END IF;
    IF OLD.amount <=> NEW.amount THEN
        SET changed = JSON_ARRAY_APPEND(changed, '$', 'amount');
    END IF;
    IF OLD.status <=> NEW.status THEN
        SET changed = JSON_ARRAY_APPEND(changed, '$', 'status');
    END IF;

    INSERT INTO orders_audit (
        table_name, operation_type, row_id,
        old_data, new_data, changed_fields,
        operator, client_ip, change_time
    ) VALUES (
        'orders',
        'UPDATE',
        NEW.id,
        JSON_OBJECT(
            'id', OLD.id,
            'customer_id', OLD.customer_id,
            'amount', OLD.amount,
            'status', OLD.status,
            'created_at', OLD.created_at
        ),
        JSON_OBJECT(
            'id', NEW.id,
            'customer_id', NEW.customer_id,
            'amount', NEW.amount,
            'status', NEW.status,
            'created_at', NEW.created_at
        ),
        changed,
        COALESCE(@current_operator, USER()),
        COALESCE(@current_client_ip, SUBSTRING_INDEX(USER(),'@',-1)),
        NOW(3)
    );
END$$

DELIMITER ;
注意：UPDATE触发器里用<=>（NULL-safe等于）而不是=。为什么？因为NULL = NULL返回NULL，而NULL <=> NULL返回1。我曾经因为这个坑，导致所有NULL字段变更都没被记录，排查了整整一个下午。

DELETE触发器相对简单，只记录旧数据：

DELIMITER $$

CREATE TRIGGER trg_orders_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit (
        table_name, operation_type, row_id,
        old_data, new_data, changed_fields,
        operator, client_ip, change_time
    ) VALUES (
        'orders',
        'DELETE',
        OLD.id,
        JSON_OBJECT(
            'id', OLD.id,
            'customer_id', OLD.customer_id,
            'amount', OLD.amount,
            'status', OLD.status,
            'created_at', OLD.created_at
        ),
        NULL,
        NULL,
        COALESCE(@current_operator, USER()),
        COALESCE(@current_client_ip, SUBSTRING_INDEX(USER(),'@',-1)),
        NOW(3)
    );
END$$

DELIMITER ;

4.3 触发器带来的性能开销

触发器不是免费的午餐。它带来的性能开销，我总结为三个维度：

开销类型 影响程度 原因分析
写放大 高 每笔DML操作额外产生一次审计表写入，IO和日志量翻倍
锁竞争 中 审计表上的自增主键和索引会成为热点，高并发下容易锁等待
事务延长 高 触发器在源事务内执行，审计表写入失败会导致源事务回滚

我在一个日活千万的电商订单系统里做过压测：不加触发器时，单表INSERT的TPS能到8000；加上三个触发器（INSERT/UPDATE/DELETE）后，TPS直接掉到3200，降幅超过60%。

怎么优化？几个实战经验：

  • 审计表用独立的表空间：避免和源表争抢磁盘IO。我习惯把审计表放在单独的SSD盘上。
  • 减少索引数量：审计表只保留必要的索引。上面那个DDL里我只建了两个索引，多了反而拖慢写入。
  • 批量提交：如果业务允许，可以在触发器里把数据先写到内存队列，再批量刷入审计表。但要注意——触发器里做异步操作有风险，事务回滚时队列里的数据就成脏数据了。
核心原则：触发器CDC适合低并发、高一致性要求的场景。如果你的源表每秒写入超过500笔，建议考虑日志解析方案（比如Debezium），而不是硬扛触发器。

4.4 基于触发器的数据校验策略

触发器写进去了，不代表数据就是对的。我见过太多案例——触发器逻辑写错了，审计表里全是垃圾数据，下游消费端还傻乎乎地照着跑。

校验策略我分三层：

  1. 行级校验：对比源表和审计表的记录数、关键字段值。比如每天凌晨跑一个脚本，统计orders表的总行数和orders_audit里INSERT+UPDATE-DELETE的净增量是否一致。
  2. 字段级校验：随机抽取1000条记录，对比old_data/new_data里的字段值和源表实际值是否匹配。这个可以用Python脚本配合数据库连接池来做。
  3. 时序校验：检查审计表的change_time是否严格递增。如果出现时间倒挂，说明触发器或数据库时钟有问题。

我曾经在一个金融项目里遇到过这样的问题：触发器里用了NOW()，但数据库主从切换后，新主库的时间比老主库慢了2秒。结果审计表里的时间戳全是乱的，下游做时序分析时全错了。后来我改成用SYSDATE()（MySQL里SYSDATE是实时获取，NOW是事务开始时间），才彻底解决。

下面这张图概括了基于触发器的CDC校验全流程：

基于触发器的CDC校验流程 基于触发器的CDC校验流程 源表 (orders) 触发器 (AFTER INSERT/UPDATE/DELETE) 审计表 (orders_audit) 行级校验 (记录数对比) 字段级校验 (随机抽样对比) 时序校验 (时间戳递增检查) 异常处理：告警 + 补偿写入 + 人工复核 说明：三层校验并行执行，任意一层发现异常即触发告警流程
避坑指南：我曾经在生产环境遇到过触发器因为字段类型不匹配（比如源表字段从INT改成BIGINT，但触发器里还是INT），导致所有UPDATE操作都报错。从那以后，我每次改源表结构，都会同步检查触发器逻辑。建议把触发器的DDL也纳入版本管理，和业务表一起走CI/CD。

最后说一句：触发器CDC虽然“笨”，但它有一个其他方案都比不了的优势——与数据库版本无关。不管你是MySQL 5.6还是8.0，不管你是RDS还是自建，触发器都能跑。对于老系统迁移、异构数据库同步这类场景，它反而是最稳的选择。

← 上一章 📖 返回目录 下一章 →