MySQL REPLACE INTO 與 INSERT ... ON DUPLICATE KEY UPDATE 核心差異深度解析

兩者皆用於「主鍵/唯一鍵衝突時的寫入策略」，但底層行為差異巨大，直接影響資料完整性與自增 ID 增長。本文通過實測案例，揭示核心區別與選型指南。

一、測試環境建立

CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(10), c VARCHAR(10));
INSERT INTO t1 VALUES (1, 'c3', 'c2'), (2, '2', '3');
INSERT INTO t1 (b, c) VALUES ('r2', 'r3');  -- a=3 自增
# 當前資料：a=1,2,3

二、REPLACE INTO 實測

1. 衝突鍵更新

REPLACE INTO t1 (a, b) VALUES (2, 'a');
# 影響行數：2 (1 刪除 + 1 插入)

結果：a=2 記錄的 c 字段變為 空字串（非 NULL，因無顯式 DEFAULT）。

2. 自增 ID 影響

INSERT INTO t1 (b, c) VALUES ('r4', 'r5');
# 新記錄 a=5（而非預期的 4）

結論：REPLACE 內部執行 DELETE + INSERT，導致自增計數器 +1。

三、INSERT ... ON DUPLICATE KEY UPDATE 實測

1. 衝突鍵更新

INSERT INTO t1 (a, b) VALUES (3, 'r5') ON DUPLICATE KEY UPDATE b='r5';
# 影響行數：2，c 字段保持原值 'r3'

結果：僅更新指定列 b，保留其他列原值。

2. 自增 ID 影響

INSERT INTO t1 (b, c) VALUES ('r6', 'r7');
# 新記錄 a=7（同樣跳過 6）

結論：ON DUPLICATE KEY UPDATE 同樣導致自增計數器 +1（MySQL 內部機制預分配自增值）。

3. 無衝突時行為

INSERT INTO t1 (a, b, c) VALUES (33, 'r5', 'c3') ON DUPLICATE KEY UPDATE b='r5';
# 正常插入，所有列生效

四、核心差異總結表

五、選型建議

• 業務需完整保留非更新列舊值 → 必選 INSERT ... ON DUPLICATE KEY UPDATE。
• 表無外鍵、且確定語句包含所有列 → 可用 REPLACE INTO 簡化邏輯、提升寫入效能。
• 高併發自增 ID 敏感場景：兩者均會導致自增 ID 空洞，若業務不能接受，需改用「先查後插/更新」或應用層生成 ID 策略。
• 觸發器依賴場景：注意觸發器類型差異（DELETE+INSERT vs UPDATE），避免邏輯錯亂。

個人傾向：優先使用 INSERT ... ON DUPLICATE KEY UPDATE，語義更清晰、資料安全性更高，效能差異在多數場景可忽略。