萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL REPLACE INTO 深度解析(下):自增主鍵下的主從不一致災難與避坑指南

災難現場重現

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

INSERT INTO t1(name) VALUES('a');  -- id=1, AUTO_INCREMENT=2
-- 主從結構同步正常,雙邊 AUTO_INCREMENT=2

Master 連續執行 REPLACE

REPLACE INTO t1(name) VALUES('a');  -- 唯一鍵衝突,DELETE id=1 → INSERT id=2
REPLACE INTO t1(name) VALUES('a');  -- 唯一鍵衝突,DELETE id=2 → INSERT id=3
-- Master 端結果:id=3, AUTO_INCREMENT=4

Slave 端 Binlog 重放

Binlog 記錄為 UPDATE(唯一鍵衝突路徑),不觸發自增計數器遞增。
Slave 端資料同步正確(id=3),但 AUTO_INCREMENT=2 未變!

主從切換瞬間的災難

原 Slave 晉升為新 Master,業務繼續執行:

REPLACE INTO t1(name) VALUES('a');
-- 新 Master 依據 AUTO_INCREMENT=2 分配 id=2
-- 結果:id 從 3 變回 2,業務若強依賴 ID 將產生嚴重邏輯錯誤!
-- 若表中有多行資料,更會引發主鍵衝突報錯。

根因剖析

  1. REPLACE INTO 唯一鍵衝突時執行 DELETE + INSERT,但 Binlog 記錄為 UPDATE。
  2. UPDATE 不修改 AUTO_INCREMENT 計數器。
  3. Slave 重放 Binlog 後,資料正確但元數據(自增值)偏移。
  4. 角色互換後,新 Master 按舊自增值分配 ID,造成 ID 回退、衝突、業務錯亂。

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

INSERT INTO t1(name) VALUES('a')
ON DUPLICATE KEY UPDATE name='a';  -- 什麼都不改,僅為觸發更新邏輯
  • 唯一鍵衝突時執行 UPDATE,Binlog 同為 UPDATE,自增計數器不變。
  • 主從雙邊 AUTO_INCREMENT 始終同步推進,切換零風險。
  • 語義清晰:不刪除舊行、不重新分配 ID、不觸發外鍵級聯刪除。

實測驗證

INSERT INTO t1(name) VALUES('a'),('b'),('c');  -- id=1,2,3, AUTO_INCREMENT=4
INSERT INTO t1(name) VALUES('a') ON DUPLICATE KEY UPDATE name='a';
-- AUTO_INCREMENT 維持 4,主從完全一致

總結建議

  1. 業務強依賴自增 ID(如外鍵關聯、URL 識別碼)嚴禁使用 REPLACE INTO。
  2. 一般場景亦不建議使用,因可能導致主鍵重組、外鍵級聯副作用。
  3. 統一採用 INSERT ... ON DUPLICATE KEY UPDATE 解決「插入或更新」需求。
  4. 若必須用 REPLACE,請移除自增主鍵改用業務主鍵,並充分測試主從切換場景。
copyright © 萬盛學電腦網 all rights reserved