業務常需求:兩張結構不同的表 (如主表 A、從表 B) 保持指定欄位同步。MySQL 觸發器可在 INSERT/UPDATE/DELETE 時自動同步對應資料。本文提供完整觸發器模板、關鍵防循環機制、工具推薦與實戰注意事項。
表 A 觸發器更新表 B → 觸發表 B 觸發器 → 更新表 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$$
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$$
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 為舊值phpMyAdmin 不支援可視化建立觸發器且易語法報錯。推薦 SQLyog (Java 開發,跨平台):
| 坑點 | 後果 | 解決方案 |
|---|---|---|
| 未加防循環變數 | 觸發器無限遞歸,連線中斷/資料膨脹 | 使用 @sync_disable 會話變數守衛 |
| 同步邏輯不冪等 | 重複同步導致主鍵衝突/資料不一致 | 使用 NOT EXISTS 或 ON DUPLICATE KEY UPDATE |
| 大事務中觸發器執行慢 | 鎖持有時間長,阻塞主表寫入 | 異步同步 (Binlog/ Canal/ Kafka) 替代觸發器 |
| DDL 變更導致觸發器失效 | 欄位新增/刪除/類型變更報錯 | 變更腳本同步更新觸發器定義 |
| 跨資料庫觸發器權限不足 | CREATE TRIGGER 權限或跨庫寫入權限缺失 | 授予 TRIGGER、INSERT/UPDATE/DELETE 權限 |
MySQL 觸發器能有效實現雙表同步,關鍵在於:防循環會話變數、冪等同步邏輯、完整覆蓋 INSERT/UPDATE/DELETE。對於核心高併發業務,建議遷移至基於 Binlog 的異步同步架構,觸發器作為過渡或輔助方案。使用 SQLyog 等專業工具可大幅降低開發除錯成本。