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;
REPLACE INTO yy VALUES(1,2,5); -- a=1 已存在
-- 執行:DELETE 舊行(1,2,3) → INSERT 新行(1,2,5)
-- Binlog:DELETE + INSERT,AUTO_INCREMENT +1
REPLACE INTO yy VALUES(5,3,6); -- 主鍵 5 無衝突,唯一鍵衝突
-- 執行:UPDATE a=4 行,將主鍵改為 5
-- Binlog:UPDATE(WHERE a=4 SET a=5)
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。將以實測案例揭示 REPLACE INTO 在主從切換時導致自增 ID 錯位、資料覆蓋的災難性後果,並給出 INSERT ... ON DUPLICATE KEY UPDATE 最佳替代方案。