您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 即時同步資料到 MySQL：Linked Server + 觸發器 + 存儲過程完整實作

編輯：MySQL 資料庫相關知識

實現 SQL Server (來源) → MySQL (目標) 的近即時單向同步，採用 Linked Server (ODBC) + AFTER 觸發器 + 存儲過程 架構，支援 INSERT/UPDATE/DELETE 三種操作同步。適用於異構資料庫整合、報表庫建立等場景。

架構總覽

SQL Server (Publisher)
   │
   ├─ Linked Server "jt" (MySQL ODBC Driver)
   ├─ Loopback Linked Server "loopback" (本機回環，防止分散式交易提升)
   └─ 觸發器 → 本機存儲過程 → OPENQUERY(jt, ...) → MySQL

步驟 1：安裝 MySQL ODBC Driver 並建立系統 DSN

下載安裝 MySQL Connector/ODBC 5.3+ (32/64 位對應 SQL Server 版本)。
ODBC 資料來源管理員 (64-bit) → 系統 DSN → 新增 → MySQL ODBC 5.3 ANSI Driver。
Data Source Name: jt，Server: 192.168.5.188，Database: suzhou，UID/PWD 填寫。

步驟 2：建立 Linked Server

-- 目標 MySQL Linked Server
EXEC sp_addlinkedserver
  @server = 'jt',
  @srvproduct = 'mysql',
  @provider = 'MSDASQL',
  @datasrc = NULL,
  @location = NULL,
  @provstr = 'DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=suzhou;UID=root;PORT=3306;',
  @catalog = NULL;

-- 登入映射
EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'jt', @useself = 'false',
  @rmtuser = 'root', @rmtpassword = 'password';

-- 允許 RPC 輸出、禁止分散式交易提升
EXEC sp_serveroption 'jt', 'rpc out', 'true';
EXEC sp_serveroption 'jt', 'remote proc transaction promotion', 'false';

步驟 3：建立 Loopback 回環 Linked Server (關鍵)

EXEC sp_addlinkedserver @server = 'loopback', @srvproduct = '', @provider = 'SQLNCLI', @datasrc = @@SERVERNAME;
EXEC sp_serveroption 'loopback', 'rpc out', 'true';
EXEC sp_serveroption 'loopback', 'remote proc transaction promotion', 'false';

目的：觸發器內呼叫本機存儲過程時，透過 Loopback 避免觸發分散式交易 (MSDTC)，大幅降低鎖定與失敗率。

步驟 4：建立同步資料表 (雙端結構一致)

-- SQL Server
CREATE DATABASE suzhou;
CREATE TABLE sz (
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  orderno CHAR(20) NOT NULL,
  ordertime DATETIME NOT NULL DEFAULT GETDATE(),
  remark VARCHAR(200)
);

-- MySQL
CREATE TABLE sz (
  id INT(11) NOT NULL,
  orderno CHAR(20) NOT NULL,
  ordertime DATETIME(6) NOT NULL,
  remark VARCHAR(200),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

步驟 5：觸發器與存儲過程 (INSERT/UPDATE/DELETE)

-- === INSERT ===
CREATE TRIGGER tr_insert_sz ON suzhou.dbo.sz
AFTER INSERT AS
BEGIN
  DECLARE @id INT, @orderno CHAR(20), @ordertime DATETIME, @remark VARCHAR(200);
  SELECT @id=id, @orderno=orderno, @ordertime=ordertime, @remark=remark FROM inserted;
  EXEC loopback.suzhou.dbo.sp_insert @id, @orderno, @ordertime, @remark;
END;
GO

CREATE PROCEDURE sp_insert
  @id INT, @orderno CHAR(20), @ordertime DATETIME, @remark VARCHAR(200)
AS
BEGIN
  SET NOCOUNT ON;
  INSERT OPENQUERY(jt, 'SELECT id, orderno, ordertime, remark FROM sz')
  VALUES (@id, @orderno, @ordertime, @remark);
END;
GO

-- === UPDATE ===
CREATE TRIGGER tr_update_sz ON suzhou.dbo.sz
AFTER UPDATE AS
BEGIN
  DECLARE @orderno CHAR(20), @remark VARCHAR(200);
  SELECT @orderno=orderno, @remark=remark FROM inserted;
  EXEC loopback.suzhou.dbo.sp_update @orderno, @remark;
END;
GO

CREATE PROCEDURE sp_update
  @orderno CHAR(20), @remark VARCHAR(200)
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE OPENQUERY(jt, 'SELECT orderno, remark FROM sz')
  SET remark = @remark WHERE orderno = @orderno;
END;
GO

-- === DELETE ===
CREATE TRIGGER tr_delete_sz ON suzhou.dbo.sz
AFTER DELETE AS
BEGIN
  DECLARE @orderno CHAR(20);
  SELECT @orderno=orderno FROM deleted;
  EXEC loopback.suzhou.dbo.sp_delete @orderno;
END;
GO

CREATE PROCEDURE sp_delete
  @orderno CHAR(20)
AS
BEGIN
  SET NOCOUNT ON;
  DELETE OPENQUERY(jt, 'SELECT orderno FROM sz') WHERE orderno = @orderno;
END;
GO

測試驗證

-- SQL Server 插入
INSERT INTO sz (orderno, remark) VALUES ('a001', 'test');
-- MySQL 查詢
SELECT * FROM sz WHERE orderno='a001';

-- 更新
UPDATE sz SET remark='updated' WHERE orderno='a001';
-- 刪除
DELETE FROM sz WHERE orderno='a001';