MySQL REPLACE INTO 實戰慘案 (二)：主從切換導致自增 ID 回退與主鍵衝突完整復現

階段二：主從切換 (備庫提升為新主)

模擬故障切換，原備庫成為新主，開始接受寫入流量。

階段三：新主繼續執行 REPLACE (悲劇發生)

-- 新主 (原備庫)
REPLACE INTO t1(name) VALUES('a'); -- UK 衝突，執行 UPDATE

關鍵差異：新主 AUTO_INCREMENT=2，UK 衝突時執行 UPDATE t1 SET id=2 WHERE id=3 (將 PK 從 3 改為 2)。
結果：表中行變為 id=2, name='a'，**自增 ID 從 3 回退到 2**！

階段四：後續業務寫入引發主鍵衝突

若表中原有多條資料 (如 id=1,2,3)，切換後新寫入的自增 ID 會從 2 開始，極大概率與現有主鍵衝突，導致 Duplicate entry '2' for key 'PRIMARY' 錯誤，業務寫入全面失敗。

根因鏈路總結

1. REPLACE INTO 遇 UK 衝突 → **UPDATE 現有行，修改 PK 為新自增值**。
2. 主庫自增計數器 **+1** (分配了新 ID)，但 Binlog 記錄 **UPDATE (舊 PK → 新 PK)**。
3. 備庫重放 Binlog → **僅執行 UPDATE，不分配新 ID，自增計數器原地踏步**。
4. 主從切換 → 新主自增計數器 **落後舊主**。
5. 新主再次 REPLACE → 分配較小 ID → **PK 回退、覆蓋舊行、主鍵衝突**。

為什麼 ROW 格式也救不了？

ROW 格式記錄「行前後映像」，備庫重放時直接應用新行映像 (含新 PK)。但備庫 **不會同步更新自增計數器** (除非啟動 innodb_autoinc_lock_mode=0 並手動干預)。主備自增值依然發散，切換後同樣面臨 ID 重疊風險。

徹底解決方案：遷移至 INSERT ... ON DUPLICATE KEY UPDATE (IODKU)

-- 遷移前
REPLACE INTO t1(name) VALUES('a');

-- 遷移後 (僅更新非鍵列，或更新業務需變更列)
INSERT INTO t1(name) VALUES('a')
ON DUPLICATE KEY UPDATE name=VALUES(name);  -- 此處 name 不變，可改為 updated_at=NOW() 等

IODKU 核心優勢

• 不修改 PK：UK 衝突時 原地 UPDATE 非鍵列，PK 保持不變，自增計數器 **不推進** (MySQL 8.0.20+ 修復舊版 Bug)。
• 主備自增值同步：主備均不推進自增，切換後無 ID 回退風險。
• 鎖競爭更小：無 DELETE+INSERT 間隙鎖，僅持有行鎖 (X Lock)，死鎖概率顯著降低。
• 語義清晰：明確表達「鍵衝突時更新屬性」而非「替換整行」。

生產遷移檢查清單

1. 全量掃描代碼庫：搜尋 REPLACE INTO、REPLACE 關鍵字 (含 ORM 框架生成 SQL)。
2. 評估業務語義：確認是否真的需要「替換整行」(含 PK 變更)？99% 場景只需更新非鍵列。
3. 改造 SQL：統一替換為 INSERT ... ON DUPLICATE KEY UPDATE col=VALUES(col), ...。
4. 測試驗證：
   • 主從環境壓測：併發寫入、主從切換、故障恢復。
   • 自增 ID 連續性檢查：切換前後 ID 分配無回退、無衝突。
   • 資料一致性校驗：pt-table-checksum / checksum table。
5. 灰度發布：先在非核心業務驗證，再推廣核心鏈路。

特殊場景：確需「替換整行含 PK」怎麼辦？

極少數業務 (如資料重組、ID 遷移) 確需變更 PK。建議：

1. 拆分為 **事務內顯式 DELETE + INSERT**，應用層控制邏輯。
2. 使用 **存儲過程** 封裝，確保主從一致性 (需啟用 log_bin_trust_function_creators)。
3. **嚴禁** 直接使用 REPLACE INTO。

結論：REPLACE INTO 在含自增主鍵 + 唯一鍵的表上是**定時炸彈**。MySQL 官方文檔雖標註「非標準語法」，但未充分警示主從風險。**請立即在代碼審核清單中加入「禁止 REPLACE INTO」規則**，統一遷移至 INSERT ... ON DUPLICATE KEY UPDATE，從根源消除主從切換資料錯亂隱患。