MySQL触发器实现双表数据同步：防循环更新设计模式

业务场景

表 A 与表 B 结构不同，需实现：向 A 插入时同步到 B，向 B 插入时同步到 A，字段映射对应，且避免触发器循环调用。

表结构示例

CREATE TABLE tableA (
    ID INT PRIMARY KEY,
    col_A1 VARCHAR(50),
    col_A2 INT,
    sync_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tableB (
    ID INT PRIMARY KEY,
    col_B1 VARCHAR(50),  -- 对应 col_A1
    col_B2 DATE,         -- 对应 col_A2 类型转换
    sync_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

触发器实现（会话变量防循环）

DELIMITER $$

-- 表A插入触发器 -> 同步到表B
CREATE TRIGGER trg_a_after_insert
AFTER INSERT ON tableA
FOR EACH ROW
BEGIN
    -- 防循环标记
    IF @sync_disable IS NULL OR @sync_disable = 0 THEN
        SET @sync_disable = 1;
        
        -- 仅当B表不存在该ID时插入（避免重复）
        IF NOT EXISTS (SELECT 1 FROM tableB WHERE ID = NEW.ID) THEN
            INSERT INTO tableB (ID, col_B1, col_B2)
            VALUES (NEW.ID, NEW.col_A1, CAST(NEW.col_A2 AS DATE));
        END IF;
        
        SET @sync_disable = 0;
    END IF;
END$$

-- 表B插入触发器 -> 同步到表A
CREATE TRIGGER trg_b_after_insert
AFTER INSERT ON tableB
FOR EACH ROW
BEGIN
    IF @sync_disable IS NULL OR @sync_disable = 0 THEN
        SET @sync_disable = 1;
        
        IF NOT EXISTS (SELECT 1 FROM tableA WHERE ID = NEW.ID) THEN
            INSERT INTO tableA (ID, col_A1, col_A2)
            VALUES (NEW.ID, NEW.col_B1, YEAR(NEW.col_B2) * 10000 + MONTH(NEW.col_B2) * 100 + DAY(NEW.col_B2));
        END IF;
        
        SET @sync_disable = 0;
    END IF;
END$$

DELIMITER ;

关键设计点解析

扩展：UPDATE/DELETE 同步

-- 同步更新
CREATE TRIGGER trg_a_after_update
AFTER UPDATE ON tableA
FOR EACH ROW
BEGIN
    IF @sync_disable = 0 THEN
        SET @sync_disable = 1;
        UPDATE tableB 
        SET col_B1 = NEW.col_A1, col_B2 = CAST(NEW.col_A2 AS DATE)
        WHERE ID = NEW.ID;
        SET @sync_disable = 0;
    END IF;
END$$

-- 同步删除（慎用，建议软删除）
CREATE TRIGGER trg_a_after_delete
AFTER DELETE ON tableA
FOR EACH ROW
BEGIN
    IF @sync_disable = 0 THEN
        SET @sync_disable = 1;
        DELETE FROM tableB WHERE ID = OLD.ID;
        SET @sync_disable = 0;
    END IF;
END$$

局限性与替代方案

• 触发器仅在同一MySQL实例内有效，跨库/跨服务器需用 Binlog 同步（Canal/Maxwell/Debezium）
• 高并发下触发器增加写入延迟，核心链路慎用
• 复杂映射逻辑建议在应用层或中间件（如 ShardingSphere）处理
• 审计要求场景建议：应用层双写 + 定时对账任务