MySQL 處理重複鍵值衝突的三大方案：REPLACE、ON DUPLICATE KEY UPDATE、IGNORE

MySQL 處理重複鍵值衝突的三大方案：REPLACE、ON DUPLICATE KEY UPDATE、IGNORE

當 INSERT 遇到 UNIQUE 或 PRIMARY KEY 衝突，預設拋出 1062 Duplicate entry 錯誤。MySQL 提供三種語法分別對應「覆蓋」、「更新」、「忽略」三種業務語義，選擇正確方案能簡化應用層邏輯並提升效能。

測試環境建立

CREATE TABLE test1 (id INT, name VARCHAR(5), type INT, PRIMARY KEY (id));
CREATE TABLE test2 (id INT, name VARCHAR(5), type INT, PRIMARY KEY (id));

INSERT INTO test1 VALUES (101,'aaa',1),(102,'bbb',2),(103,'ccc',3);
INSERT INTO test2 VALUES (201,'aaa',1),(202,'bbb',2),(203,'ccc',3),(101,'xxx',5);

test1 3 筆，test2 4 筆，id=101 為衝突鍵。

方案一：REPLACE INTO — 刪除舊行再插入新行

REPLACE INTO test1 (id, name) SELECT id, name FROM test2;

結果：

• id=101 原行被刪除，新行 (101, 'xxx', NULL) 插入（type 未指定變 NULL）。
• 非衝突鍵 201,202,203 正常插入，type 同樣為 NULL。

完整欄位版：

REPLACE INTO test1 (id, name, type) SELECT id, name, type FROM test2;

此時 type 保留來源值，id=101 變為 (101, 'xxx', 5)。

特性總結：

• 本質為 DELETE + INSERT，觸發 BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT 觸發器。
• 自增 ID 會重新產生（若主鍵為 AUTO_INCREMENT）。
• 所有未指定欄位重置為預設值或 NULL。

方案二：INSERT ... ON DUPLICATE KEY UPDATE — 就地更新指定欄位

INSERT INTO test1 (id, name, type) SELECT id, name, type FROM test2
ON DUPLICATE KEY UPDATE test1.name = test2.name;

結果：

• id=101 原行保留，name 更新為 'xxx'，type 維持原值 1（未在 UPDATE 列表中）。
• 非衝突鍵正常插入。

特性總結：

• 不刪除原行，保留未指定欄位原值，適合「部分欄位同步」場景。
• 可在 UPDATE 子句中使用 VALUES(col) 參考插入值：UPDATE type = VALUES(type)。
• 不觸發 DELETE 觸發器，僅觸發 INSERT/UPDATE 觸發器。
• LAST_INSERT_ID() 回傳衝突行的主鍵值（MySQL 5.1+）。

方案三：INSERT IGNORE — 靜默忽略衝突

INSERT IGNORE INTO test1 (id, name, type) SELECT id, name, type FROM test2;

結果：

• id=101 衝突，略過不處理，保留原行 (101, 'aaa', 1)。
• 非衝突鍵正常插入。
• 回傳 Query OK, 3 rows affected（僅計入成功插入行數）。

特性總結：

• 適用於「只補齊缺失資料，既有資料不動」的冪等導入場景。
• 也會忽略其他錯誤（如資料截斷、外鍵違反），使用時需確認資料品質。
• 不觸發任何觸發器（因未實際執行 INSERT）。

三方案對照決策表

效能與鎖行為補充

• REPLACE：需獲取排他鎖刪除舊行再插入，高併發下鎖競爭較大。
• ON DUPLICATE KEY UPDATE：單語句原子操作，鎖持有時間最短，高併發首選。
• IGNORE：檢測到衝突即釋放鎖，吞吐最高，但需容忍「靜默失敗」。