萬盛學電腦網

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

MySQL REPLACE INTO 深度解析(上):原理、行為與 Binlog 真相

語法定位

REPLACE INTO 是 MySQL 專有擴充,語義為「嘗試插入,若主鍵/唯一鍵衝突則刪除舊行再插入新行」。常被誤以為等同 INSERT ... ON DUPLICATE KEY UPDATE,實則行為差異巨大,尤其涉及自增主鍵時更易引發主從不一致。

場景一:僅有主鍵,無唯一鍵

CREATE TABLE yy (id BIGINT PRIMARY KEY, name VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO yy VALUES(1,'abc');
REPLACE INTO yy VALUES(2,'bbb');  -- 新主鍵,執行 INSERT
REPLACE INTO yy VALUES(1,'ccc');  -- 主鍵衝突,執行 UPDATE(原地修改 name)

Binlog 記錄為 UPDATE 事件,AUTO_INCREMENT 不變。

場景二:主鍵 + 唯一鍵 共存

CREATE TABLE yy (
  a INT PRIMARY KEY,
  b INT, c INT,
  UNIQUE KEY uk_bc (b,c)
) ENGINE=InnoDB;

情形 1:主鍵衝突

REPLACE INTO yy VALUES(1,2,5);  -- a=1 已存在
-- 執行:DELETE 舊行(1,2,3) → INSERT 新行(1,2,5)
-- Binlog:DELETE + INSERT,AUTO_INCREMENT +1

情形 2:唯一鍵衝突 (b,c)=(3,6) 已存在於 a=4

REPLACE INTO yy VALUES(5,3,6);  -- 主鍵 5 無衝突,唯一鍵衝突
-- 執行:UPDATE a=4 行,將主鍵改為 5
-- Binlog:UPDATE(WHERE a=4 SET a=5)

情形 3:主鍵與唯一鍵雙雙衝突

REPLACE INTO yy VALUES(1,3,6);  -- a=1 衝突,(b,c)=(3,6) 與 a=5 衝突
-- 執行:DELETE a=1 行 → UPDATE a=5 行主鍵改為 1
-- 影響行數 = 3(1 delete + 1 update + 1 insert 邏輯)
-- Binlog:DELETE + UPDATE

核心結論

  • 無衝突 → INSERT。
  • 主鍵衝突 → DELETE + INSERT(自增 ID 會遞增)。
  • 唯一鍵衝突(主鍵不衝突) → UPDATE 修改主鍵值。
  • 雙衝突 → DELETE + UPDATE。
  • Binlog 記錄的物理操作(DELETE/INSERT/UPDATE)與邏輯語義不一定一致,這是主從不一致的溫床。

下篇預告

將以實測案例揭示 REPLACE INTO 在主從切換時導致自增 ID 錯位、資料覆蓋的災難性後果,並給出 INSERT ... ON DUPLICATE KEY UPDATE 最佳替代方案。

copyright © 萬盛學電腦網 all rights reserved