前言

REPLACE INTO 是 MySQL 特有的「存在即更新，不存在即插入」語法，看似便捷，實則暗藏 刪除舊行 + 插入新行 的語義陷阱。本文通過實測揭示其真實行為，對比 INSERT ... ON DUPLICATE KEY UPDATE，給出生產環境選型建議。

語法與官方文檔語義

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_list)] VALUES (value_list)...
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col=val...
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_list)] SELECT ...

• 需表有 PRIMARY KEY 或 UNIQUE INDEX，否則退化為普通 INSERT
• 新行與現有行在 PK/UK 上衝突 → 刪除舊行 → 插入新行
• 返回值 = 刪除行數 + 插入行數（1 表示純插入，>1 表示有替換）
• 需同時擁有 INSERT 和 DELETE 權限

實測驗證：到底是 UPDATE 還是 DELETE+INSERT？

場景 1：無自增主鍵，指定 PK 值

CREATE TABLE users(user_id INT PRIMARY KEY, user_name VARCHAR(64));
INSERT INTO users VALUES (1,'aa'),(2,'bb');
REPLACE INTO users(user_id,user_name) VALUES (1,'cc');
-- 返回 2 rows affected
SELECT * FROM users;  -- id=1 變為 'cc'，id=2 保持 'bb'

現象像更新，但無法區分是否真刪除重插。

場景 2：自增主鍵 + 唯一索引（關鍵實測）

CREATE TABLE users2(user_id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(64), UNIQUE KEY uq_name(user_name));
INSERT INTO users2 VALUES (1,'aa'),(2,'bb'),(3,'cc');
-- AUTO_INCREMENT=4

測試 A：REPLACE 指定 user_name 衝突

REPLACE INTO users2(user_name) VALUES ('dd');
-- 返回 2 rows affected
SELECT * FROM users2;
-- 結果：id=2(bb), id=3(cc), id=4(dd)  ← id=1 被刪除，新行 id=4 自增！
SHOW CREATE TABLE users2;  -- AUTO_INCREMENT=5

鐵證：REPLACE INTO 觸發唯一鍵衝突時，刪除舊行（含自增 ID）→ 插入新行（新自增 ID），而非原地 UPDATE。

測試 B：REPLACE 指定 PK 衝突

REPLACE INTO users2(user_id,user_name) VALUES (1,'dd');
-- 返回 2 rows affected，id=1 保持，user_name 變 'dd'，AUTO_INCREMENT 不變

指定 PK 時，因 PK 值相同，新行復用舊 PK，表現如 UPDATE，但本質仍是 DELETE+INSERT。

三大核心陷阱

陷阱	後果	風險等級
自增 ID 變更	關聯表外鍵失效、業務邏輯依賴 ID 失效、自增值快速耗盡（INT 上限 21億）	🔴 嚴重
觸發器雙重觸發	先觸發 BEFORE/AFTER DELETE，再觸發 BEFORE/AFTER INSERT，副作用翻倍	🟠 高
權限要求高	需 DELETE 權限，最小權限原則違背	🟡 中

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

INSERT INTO users2(user_name) VALUES ('dd')
ON DUPLICATE KEY UPDATE user_name=VALUES(user_name);
-- 返回 0 rows affected（已存在不修改），或 2 rows affected（真正更新）
-- 自增 ID 不變、不觸發 DELETE 觸發器、只需 INSERT/UPDATE 權限

• 真正的原地更新，保持主鍵穩定
• VALUES(col) 引用插入值，支援複雜表達式
• 返回值：1=新插入，2=更新，0=重複但值相同無變更

場景化選型建議

業務場景	推薦語法	理由
日志/監控數據，允許 ID 變更	REPLACE INTO	簡潔、語義清晰
核心業務表，主鍵被外鍵引用	INSERT ... ON DUPLICATE KEY UPDATE	保護主鍵穩定、避免級聯失效
需部分列更新、保留其他列	INSERT ... ON DUPLICATE KEY UPDATE	精確控制更新列
批量導入去重	INSERT IGNORE / ON DUPLICATE KEY UPDATE	IGNORE 靜默跳過，UPDATE 可合併邏輯

總結

REPLACE INTO 本質是 DELETE + INSERT，而非 UPDATE。自增主鍵場景下會導致 ID 變更、外鍵斷裂、自增值加速耗盡。生產環境核心業務表嚴禁使用 REPLACE INTO，請改用 INSERT ... ON DUPLICATE KEY UPDATE 實現真正的原地更新。僅在無關聯、無觸發器、ID 可變的邊緣表（如臨時表、日志表）中謹慎使用。