MySQL REPLACE INTO 深度解析 (一)：核心原理、執行邏輯與隱藏陷阱

REPLACE INTO 是 MySQL 獨有的「插入或更新」語法，廣泛用於業務「不存在則插入，存在則覆蓋」場景。但其底層實現機制 (Delete+Insert 或 Update) 在主鍵、唯一鍵、自增 ID 交互下極易引發主從不一致、自增 ID 跳躍、效能退化等隱性問題。本文通過源碼級 Binlog 分析，徹底拆解其三大衝突場景下的真實行為。

核心結論先行

關鍵風險：唯一鍵衝突時執行 UPDATE，但 Binlog 記錄為 UPDATE，導致備庫不執行 INSERT，自增計數器不推進，主備自增值發散 — 這是主從切換後資料錯亂的元兇。

場景一：僅有主鍵 (PK)，無唯一鍵

CREATE TABLE `yy` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

• REPLACE INTO yy VALUES(2,'bbb') → 無衝突，**INSERT**，影響 1 行。
• REPLACE INTO yy VALUES(1,'ccc') → PK=1 存在，**DELETE 舊行 + INSERT 新行**，影響 2 行。Binlog 顯式記錄 DELETE 與 INSERT。

場景二：同時存在 PK 與聯合唯一鍵 (UK)

CREATE TABLE `yy` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB;

情形 1：PK 衝突 (a=1 存在)

REPLACE INTO yy VALUES(1,2,5); -- 原有 (1,2,3)

→ **DELETE (1,2,3) + INSERT (1,2,5)**，影響 2 行。Binlog 記錄 DELETE + INSERT。

情形 2：UK 衝突 (b=3,c=6 存在，PK=5)，新插入 (5,3,6) → PK 不衝突，UK 衝突

REPLACE INTO yy VALUES(5,3,6); -- 原有 (4,3,6)

→ **UPDATE PK=4 行 修改 PK 為 5**，影響 2 行。Binlog 記錄為 UPDATE (WHERE PK=4 SET PK=5)，非 INSERT。備庫應用 Binlog 時僅執行 UPDATE，**自增計數器不遞增**，導致主備 AUTO_INCREMENT 值發散。

情形 3：PK 與 UK 同時衝突

REPLACE INTO yy VALUES(1,3,6); -- 原有 PK=1 行 (1,2,4) 及 UK 行 (5,3,6)

→ **DELETE PK=1 行 + UPDATE PK=5 行 (修改 PK 為 1)**，影響 3 行。Binlog 記錄 DELETE + UPDATE。

Binlog 格式對主從的影響

• STATEMENT 格式：上述邏輯直接同步，UK 衝突導致備庫自增值不增，主備發散。
• ROW 格式：記錄行變更前後映像，備庫重放行變更，**自增值由備庫自身生成**，同樣會導致主備自增值不一致 (主庫自增已推進，備庫未推進)。
• MIXED 格式：視具體情況切換，風險依存。

生產環境血淚教訓

1. 自增 ID 業務強依賴 (如訂單號、用戶 ID 外鍵關聯) → 嚴禁使用 REPLACE INTO。
2. 高併發寫入 → DELETE+INSERT 產生間隙鎖/插入意向鎖競爭，UPDATE 產生行鎖，死鎖概率遠高於 INSERT ... ON DUPLICATE KEY UPDATE (IODKU)。
3. 主從切換後：備庫提升為主，自增值較小，後續 REPLACE 觸發 UK 衝突時會覆蓋舊資料 (ID 回退)，導致主鍵衝突或資料覆蓋。

最佳替代方案：INSERT ... ON DUPLICATE KEY UPDATE (IODKU)

INSERT INTO t1 (name) VALUES ('a')
ON DUPLICATE KEY UPDATE name='a';  -- 或 UPDATE 實際需修改的列

• PK 衝突/UK 衝突均走 **UPDATE 路徑**，不改變 PK 值，不推進自增計數器 (MySQL 8.0.20+ 修復了 IODKU 導致自增增長的 Bug，舊版本需注意)。
• 語義明確：只更新非鍵列，避免鍵值變更引發的連鎖反應。
• 效能優於 REPLACE (無 DELETE/INSERT 開銷，鎖競爭更小)。

下篇將通過實戰案例演示 主從切換後 REPLACE INTO 導致自增 ID 回退、主鍵衝突的完整復現過程，並給出生產環境遷移改造清單。