萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 觸發器實現雙表資料同步:完整方案與避坑指南

前言

業務常需求:兩張結構不同的表 (如主表 A、從表 B) 保持指定欄位同步。MySQL 觸發器可在 INSERT/UPDATE/DELETE 時自動同步對應資料。本文提供完整觸發器模板、關鍵防循環機制、工具推薦與實戰注意事項。

核心挑戰:避免觸發器循環調用

表 A 觸發器更新表 B → 觸發表 B 觸發器 → 更新表 A → 無限循環。解決方案:會話變數標記。

方案一:雙向同步通用模板 (INSERT/UPDATE/DELETE)

表 A 觸發器

DELIMITER $$
CREATE TRIGGER `trg_a_sync_to_b` AFTER INSERT ON `a`.`table1`
FOR EACH ROW
BEGIN
    SET @sync_disable = 1;
    IF @sync_disable = 1 AND NOT EXISTS (SELECT 1 FROM `b`.`table2` WHERE ID = NEW.ID) THEN
        INSERT INTO `b`.`table2` (ID, 對應欄位1) VALUES (NEW.ID, NEW.對應欄位1);
    END IF;
    SET @sync_disable = 0;
END$$
DELIMITER ;

-- UPDATE 觸發器
CREATE TRIGGER `trg_a_upd_sync_to_b` AFTER UPDATE ON `a`.`table1`
FOR EACH ROW
BEGIN
    SET @sync_disable = 1;
    IF @sync_disable = 1 AND NEW.id != OLD.id THEN
        UPDATE `b`.`table2` SET `b`.`table2`.id = NEW.id WHERE `b`.`table2`.val = OLD.val;
    END IF;
    SET @sync_disable = 0;
END$$

-- DELETE 觸發器
CREATE TRIGGER `trg_a_del_sync_to_b` AFTER DELETE ON `a`.`table1`
FOR EACH ROW
BEGIN
    SET @sync_disable = 1;
    DELETE FROM `b`.`table2` WHERE `b`.`table2`.主鍵 IN (SELECT OLD.主鍵);
    SET @sync_disable = 0;
END$$

表 B 觸發器 (對稱定義)

CREATE TRIGGER `trg_b_sync_to_a` AFTER INSERT ON `b`.`table2`
FOR EACH ROW
BEGIN
    SET @sync_disable = 1;
    IF @sync_disable = 1 AND NOT EXISTS (SELECT 1 FROM `a`.`table1` WHERE ID = NEW.ID) THEN
        INSERT INTO `a`.`table1` (ID, 對應欄位1) VALUES (NEW.ID, NEW.對應欄位1);
    END IF;
    SET @sync_disable = 0;
END$$

方案二:標準觸發器寫法 (SQL Server 風格,MySQL 相容)

同步新增

CREATE TRIGGER trigger_name
ON A表
AFTER INSERT
AS
BEGIN
    INSERT INTO B表 (B欄位1, B欄位2, B欄位3)
    SELECT A欄位1, A欄位2, A欄位3 FROM INSERTED;
END

同步刪除

CREATE TRIGGER trigger_name
ON A表
AFTER DELETE
AS
BEGIN
    DELETE B表
    WHERE B表主鍵 IN (SELECT A表主鍵 FROM DELETED);
END

同步更新

CREATE TRIGGER trigger_name
ON A表
AFTER UPDATE
AS
BEGIN
    UPDATE B表
    SET B.B欄位1 = A.A欄位1
    FROM B表 AS B, INSERTED AS A
    WHERE B.B表主鍵 = A.A表主鍵;
END

關鍵概念:INSERTED 與 DELETED 虛擬表

  • INSERTED:包含觸發操作後的新記錄 (INSERT/UPDATE 時可用)
  • DELETED:包含觸發操作前的舊記錄 (DELETE/UPDATE 時可用)
  • UPDATE 觸發器中:INSERTED 為新值,DELETED 為舊值

工具推薦:SQLyog

phpMyAdmin 不支援可視化建立觸發器且易語法報錯。推薦 SQLyog (Java 開發,跨平台):

  • 圖形化建立觸發器,自動生成標準 SQL
  • 支援語法高亮、格式化、執行計劃分析
  • 生成的 SQL 可直接在 phpMyAdmin/命令列執行

實戰避坑指南

坑點後果解決方案
未加防循環變數觸發器無限遞歸,連線中斷/資料膨脹使用 @sync_disable 會話變數守衛
同步邏輯不冪等重複同步導致主鍵衝突/資料不一致使用 NOT EXISTS 或 ON DUPLICATE KEY UPDATE
大事務中觸發器執行慢鎖持有時間長,阻塞主表寫入異步同步 (Binlog/ Canal/ Kafka) 替代觸發器
DDL 變更導致觸發器失效欄位新增/刪除/類型變更報錯變更腳本同步更新觸發器定義
跨資料庫觸發器權限不足CREATE TRIGGER 權限或跨庫寫入權限缺失授予 TRIGGER、INSERT/UPDATE/DELETE 權限

效能與架構建議

💡 何時選擇觸發器 vs 異步同步

  • 觸發器適合:強一致性要求、資料量小/中、同步邏輯簡單、表數量少
  • 異步同步適合:最終一致性可接受、高併發寫入、複雜轉換邏輯、多下游系統、跨機房
  • 異步方案:Canal 解析 Binlog → Kafka/RocketMQ → 消費者寫入目標表

總結

MySQL 觸發器能有效實現雙表同步,關鍵在於:防循環會話變數、冪等同步邏輯、完整覆蓋 INSERT/UPDATE/DELETE。對於核心高併發業務,建議遷移至基於 Binlog 的異步同步架構,觸發器作為過渡或輔助方案。使用 SQLyog 等專業工具可大幅降低開發除錯成本。

copyright © 萬盛學電腦網 all rights reserved