MySQL REPLACE INTO 深度解析：語法、陷阱與最佳替代方案

核心機制

REPLACE INTO = INSERT + DELETE（衝突時）。流程：

1. 嘗試插入新行。
2. 若主鍵/唯一索引衝突 → 刪除舊行 → 重新插入新行。

返回值 = 刪除行數 + 插入行數。單行返回 1 表示純插入，>1 表示有替換發生。

三種語法形式

-- 1. VALUES 形式
REPLACE INTO tbl (col1, col2) VALUES (v1, v2), (v3, v4);

-- 2. SET 形式
REPLACE INTO tbl SET col1=v1, col2=v2;

-- 3. SELECT 形式
REPLACE INTO tbl (col1, col2) SELECT col1, col2 FROM src_tbl;

⚠ 三大生產環境禁忌

1. 自增主鍵會改變，破壞外鍵關聯

舊行刪除、新行插入，自增 ID 重新分配。所有引用該 ID 的外鍵關聯瞬間失效。

2. 高頻 REPLACE 導致主鍵耗盡

每次替換消耗一個自增值。長期高頻操作會讓 BIGINT 甚至 INT 達到上限，導致無法寫入。

3. 觸發器雙重觸發

BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT 全部會執行，副作用不可控。

對比：REPLACE INTO vs INSERT ... ON DUPLICATE KEY UPDATE

最佳實踐建議

• 首選 INSERT ... ON DUPLICATE KEY UPDATE：保留主鍵、不觸發 DELETE 觸發器、效能更優。
• 若必須用 REPLACE，表不能有外鍵被引用，且主鍵用 BIGINT 並監控剩餘值。
• 權限要求：同時擁有 INSERT 與 DELETE 權限。

參考來源：Martin 的技術博客