MySQL REPLACE 語法全解析：當 INSERT 遇上主鍵衝突自動轉 UPDATE

MySQL 的 REPLACE 語句是 INSERT 與 DELETE + INSERT 的原子化結合，專為「插入新資料，若主鍵/唯一鍵衝突則覆蓋」場景設計。本文深入解析其語法、行為邏輯、回傳值含義與實戰陷阱。

核心機制：原子化的「刪除舊 + 插入新」

當嘗試插入的資料與表中現有資料在 主鍵 或 唯一索引 上衝突時：

1. MySQL 先 刪除 衝突的現有行
2. 再 插入 新資料行
3. 整個過程在單一語句中完成，無需顯式事務

前置條件：必須有主鍵或非空唯一索引

• 表必須定義 PRIMARY KEY 或 UNIQUE NOT NULL 索引
• 若無唯一約束，REPLACE 行為等同 INSERT
• 唯一索引欄位不能允許 NULL，否則無法判斷唯一性

語法變體：三種寫法任君選擇

1. VALUES 形式（最常用）

-- 單筆
REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50);

-- 多筆批次
REPLACE INTO users (id, name, age) 
VALUES (123, '趙本山', 50), (134, 'Mary', 15);

2. SET 形式（欄位值配對更清晰）

REPLACE INTO users SET id = 123, name = '趙本山', age = 50;

3. SELECT 形式（從其他表複製資料）

REPLACE INTO target_table (id, name, age)
SELECT id, name, age FROM source_table WHERE ...

回傳值解讀：受影響行數的隱含資訊

回傳值	含義
1	無衝突，純粹 INSERT** 1 行
2	1 行衝突：DELETE 1 + INSERT 1
> 2	多個唯一索引衝突：刪除 N 行 + INSERT 1 行，總計 N+1

多重唯一索引的連鎖刪除效應

當表有多個唯一索引時，REPLACE 會檢查每個索引，所有衝突行全部刪除，再插入新行。

CREATE TABLE t1 (
    a INT NOT NULL UNIQUE,
    b INT NOT NULL UNIQUE,
    c INT NOT NULL UNIQUE
);

INSERT INTO t1 VALUES (1,1,1), (2,2,2), (3,3,3);

REPLACE INTO t1 (a,b,c) VALUES (1,2,3);
-- 結果：3 行被刪除 (a=1, b=2, c=3 分別衝突) + 1 行插入 = 4 rows affected
-- 表中僅剩：(1,2,3)

REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE

特性	REPLACE	INSERT ... ON DUPLICATE KEY UPDATE
語法複雜度	簡單，同 INSERT	需列出更新欄位
衝突處理	整行刪除重插	就地更新指定欄位
自增 ID 行為	可能改變 (刪除重插)	保持不變
觸發器觸發	DELETE + INSERT 觸發器	UPDATE 觸發器
外鍵關聯	可能違反外鍵 (先刪除)	較安全，僅更新

實戰注意事項與避坑指南

• 自增主鍵會重置：刪除重插導致 AUTO_INCREMENT 值變化，影響依賴 ID 的業務邏輯
• 外鍵約束風險：若被其他表參考，DELETE 階段可能失敗 (除非 ON DELETE CASCADE)
• 觸發器雙重觸發：會同時火燒 BEFORE/AFTER DELETE 與 BEFORE/AFTER INSERT
• 效能考量：大量資料時，REPLACE 比 INSERT ... ON DUPLICATE KEY UPDATE 慢（涉及刪除操作與索引維護）
• 僅限 MySQL：非標準 SQL，移植性差

適用場景總結

適合：日誌表、快照表、配置表、單鍵唯一且無外鍵依賴的覆蓋寫入。
不適合：有外鍵關聯的主表、需保持自增 ID 穩定的業務表、高併發寫入熱點表。

理解 REPLACE 的「刪除重插」本質，才能在正確場景發揮其簡潔原子化的優勢，避免踩中自增 ID 漂移、外鍵衝突等隱形坑。