MySQL 觸發器實時同步到 SQL Server：異構數據庫同步實戰方案

MySQL 觸發器實時同步到 SQL Server：異構數據庫同步實戰方案

業務需求：MySQL 核心表 accounts 變更（增/刪/改）實時同步至 SQL Server 同名表。方案：MySQL 觸發器寫入備份表 + SQL Server Agent 定時拉取備份表增量 + 存儲過程合并寫入目標表。

架構圖解

MySQL accounts 表
    │  AFTER INSERT/UPDATE/DELETE Trigger
    ▼
MySQL accountsbackup 表（含 BehaviorType: I/U/D, ExecutingState, ModificationTime）
    │  SQL Server Linked Server (OpenQuery) 定時輪詢
    ▼
SQL Server 存儲過程 trigger_account
    │  分三步：INSERT(I) / DELETE(D) / UPDATE(U)
    ▼
SQL Server account 表

MySQL 端：觸發器與備份表

1. 備份表結構（關鍵字段）

CREATE TABLE `accountsbackup` (
  `id` char(36) NOT NULL,
  ...  -- 所有業務字段同步
  `BehaviorType` varchar(45) DEFAULT NULL,  -- 'I' Insert, 'U' Update, 'D' Delete
  `ExecutingState` varchar(45) DEFAULT NULL, -- NULL=待同步, 'D'=已同步
  `ModificationTime` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：id 不設唯一索引（同一記錄多次變更產生多行）。

2. 三個觸發器（增/刪/改）

-- 插入觸發器
DELIMITER ||
CREATE TRIGGER t_afterinsert_on_accounts AFTER INSERT ON accounts
FOR EACH ROW BEGIN
  INSERT INTO accountsbackup (id, name, ..., BehaviorType)
  VALUES (NEW.id, NEW.name, ..., 'I');
END||

-- 更新觸發器
CREATE TRIGGER t_afterupdate_on_accounts AFTER UPDATE ON accounts
FOR EACH ROW BEGIN
  INSERT INTO accountsbackup (id, name, ..., BehaviorType)
  VALUES (NEW.id, NEW.name, ..., 'U');
END||

-- 刪除觸發器
CREATE TRIGGER t_afterdelete_on_accounts AFTER DELETE ON accounts
FOR EACH ROW BEGIN
  INSERT INTO accountsbackup (id, name, ..., BehaviorType)
  VALUES (OLD.id, OLD.name, ..., 'D');
END||
DELIMITER ;

SQL Server 端：Linked Server 與存儲過程

1. 目標表結構

CREATE TABLE [dbo].[account] (
  [id] [char](36) NOT NULL,
  [name] [varchar](150) NULL,
  ...  -- 字段類型映射：char→char, varchar→varchar, text→text, tinyint→smallint
  PRIMARY KEY ([id])
);

2. 核心存儲過程 trigger_account 邏輯

ALTER PROCEDURE [dbo].[trigger_account] AS
BEGIN
  -- 1. 處理插入 (BehaviorType='I' 且 ExecutingState IS NULL)
  INSERT INTO account (id, name, ...)
  SELECT rtrim(id), name, ...  -- 關鍵：RTRIM 去除 CHAR 尾部空格
  FROM OPENQUERY(MySQL, 'SELECT ... FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''I''');
  
  UPDATE OPENQUERY(MySQL, 'SELECT * FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''I''')
  SET ExecutingState='D', ModificationTime=GETDATE();

  -- 2. 處理刪除 (BehaviorType='D')
  SELECT rtrim(id) INTO #temp FROM OPENQUERY(MySQL, 'SELECT id FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''D''');
  IF (SELECT COUNT(*) FROM #temp) > 0 BEGIN
    DELETE FROM account WHERE id IN (SELECT rtrim(id) FROM #temp);
    UPDATE OPENQUERY(MySQL, 'SELECT * FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''D''')
    SET ExecutingState='D', ModificationTime=GETDATE();
  END

  -- 3. 處理更新 (BehaviorType='U') - 最複雜：UPSERT 邏輯
  SELECT rtrim(id) id, name, ... INTO #temp1 FROM OPENQUERY(MySQL, 'SELECT ... FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''U''');
  IF (SELECT COUNT(*) FROM #temp1) > 0 BEGIN
    -- 存在則更新
    UPDATE account SET ... FROM account INNER JOIN #temp1 t ON account.id = t.id;
    -- 不存在則插入（極少見，但需處理）
    INSERT INTO account (...) SELECT ... FROM #temp1 t WHERE t.id NOT IN (SELECT id FROM account);
    UPDATE OPENQUERY(MySQL, 'SELECT * FROM accountsbackup WHERE ExecutingState IS NULL AND BehaviorType=''U''')
    SET ExecutingState='D', ModificationTime=GETDATE();
  END
END

關鍵技術點與避坑

1. CHAR 類型尾部空格：MySQL CHAR(36) 存 UUID 不補空格，SQL Server CHAR(36) 會右填空格。**必須 RTRIM**，否則 JOIN/WHERE 失敗。
2. OpenQuery 權限：Linked Server 登錄賬號需 MySQL SELECT、UPDATE（更新 ExecutingState）權限。
3. 幂等性：ExecutingState 狀態機保證重複執行不重複同步。
4. 調度頻率：SQL Server Agent 作業每 10 秒/分鐘執行，延遲可控。
5. 大事務風險：批量同步時拆分批次，避免長事務鎖表。
6. DDL 同步：觸發器不捕獲 DDL，結構變更需人工同步雙邊表結構。

優化替代方案（現代架構）

• Canal + Kafka + Flink/Sink：解析 Binlog → 消息隊列 → 異構寫入，延遲秒級、解耦、可擴展。
• Debezium (CDC)：Kafka Connect 生態，標準化異構同步。
• DataX / CloudCanal：企業級異構同步工具，支持全量+增量、Schema 演進。

觸發器+輪詢方案適合表少、延遲容忍度高、無中間件條件的輕量級場景；核心業務建議上 CDC 平台。