MySQL REPLACE INTO 深度解析（二）：主從切換導致資料不一致的血淚教訓

核心風險：自增主鍵 + 唯一鍵 + REPLACE = 主從不一致

當表同時擁有 自增主鍵 與 唯一鍵，且使用 REPLACE INTO 處理唯一鍵衝突時，主庫與備庫的 AUTO_INCREMENT 計數器會發生偏移，主從切換瞬間引發資料覆蓋或主鍵衝突。

複現案例

CREATE TABLE t1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20),
  UNIQUE KEY uk_name (name)
) ENGINE=InnoDB;

步驟 1：初始化

主備 AUTO_INCREMENT=2，插入 ('a') → id=1。

步驟 2：主庫連續 REPLACE

REPLACE INTO t1(name) VALUES('a');  -- UK 衝突，UPDATE id=1，AUTO_INCREMENT→3
REPLACE INTO t1(name) VALUES('a');  -- 再次 UPDATE，AUTO_INCREMENT→4

主庫狀態：id=3, name='a'，AUTO_INCREMENT=4

步驟 3：備庫狀態

備庫應用 binlog（記錄為 UPDATE），AUTO_INCREMENT 維持 2 不變，資料行 id=3 同步成功。

關鍵差異：主庫 AUTO_INCREMENT=4，備庫 AUTO_INCREMENT=2。

步驟 4：主從切換災難

備庫升為新主庫，業務繼續執行 REPLACE INTO t1(name) VALUES('a')：

• 新主庫按 AUTO_INCREMENT=2 分配 ID
• UK 衝突觸發 UPDATE，將現有 id=3 改為 id=2
• 資料發生覆蓋：id 從 3 變為 2，業務邏輯若依賴 ID 將崩潰

若表中有多行資料，後續寫入更會引發主鍵衝突錯誤，導致寫入失敗。

根因分析

REPLACE INTO 處理 UK 衝突時執行的是 UPDATE，但自增計數器在「DELETE+INSERT」邏輯中才會推進。binlog 記錄為 UPDATE 不會推進備庫計數器，導致主備計數器偏移。

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

INSERT INTO t1(name) VALUES('a')
ON DUPLICATE KEY UPDATE name=VALUES(name);

• UK 衝突時執行 真正的 UPDATE，不涉及主鍵變更
• 自增計數器不會錯誤推進
• 主備行為完全一致，支援安全切換

總結建議

1. 嚴禁在含自增主鍵的表上使用 REPLACE INTO
2. 統一改用 INSERT ... ON DUPLICATE KEY UPDATE
3. 若業務強依賴自增 ID 連續性，甚至應避免任何可能導致「洞」的操作