「覆蓋寫入」兩大神器的本質區別與選型指南

MySQL 提供兩種「存在則更新、不存在則插入」語法：REPLACE INTO 與 INSERT ... ON DUPLICATE KEY UPDATE。它們在主鍵/唯一鍵衝突處理、自增 ID 行為、觸發器觸發、性能上有本質不同。

核心差異對照

特性	REPLACE INTO	INSERT ... ON DUPLICATE KEY UPDATE
衝突處理	DELETE 舊行 + INSERT 新行	UPDATE 現有行
自增 ID	重新分配（舊 ID 丟失）	保持不變
觸發器	觸發 BEFORE/AFTER DELETE + INSERT	觸發 BEFORE/AFTER UPDATE
外鍵關聯	可能破壞（CASCADE DELETE）	安全
性能	較慢（兩次操作）	較快（就地更新）
語法靈活性	全列覆蓋	可部分更新、引用舊值 VALUES(col)

實測演示

DROP TABLE IF EXISTS t;
CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    UNIQUE KEY uk_name (name)
);

-- REPLACE INTO
REPLACE INTO t (name) VALUES ('first');  -- id=1
REPLACE INTO t (name) VALUES ('first');  -- id=2 （ID 變了！）

-- INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO t (name) VALUES ('second');  -- id=3
INSERT INTO t (name) VALUES ('second') ON DUPLICATE KEY UPDATE name='second2';  -- id=3 不變

進階用法：ON DUPLICATE KEY UPDATE 靈活控制

-- 部分列更新
INSERT INTO t (name, score) VALUES ('alice', 100)
ON DUPLICATE KEY UPDATE score = score + VALUES(score), updated_at = NOW();

-- 多行批次
INSERT INTO t (name, score) VALUES ('a',1),('b',2),('c',3)
ON DUPLICATE KEY UPDATE score = VALUES(score);

選型決策樹

1. 需保持自增 ID 不變 → INSERT ... ON DUPLICATE KEY UPDATE
2. 表有外鍵被引用 → INSERT ... ON DUPLICATE KEY UPDATE
3. 需觸發 UPDATE 觸發器 → INSERT ... ON DUPLICATE KEY UPDATE
4. 極簡全列覆蓋、無外鍵、不在乎 ID 變化 → REPLACE INTO 可用
5. 批量導入、ETL 管道 → 首選 INSERT ... ON DUPLICATE KEY UPDATE

性能微基準（萬級測試）

-- 測試表
CREATE TABLE bench (id INT PRIMARY KEY, val INT, UNIQUE KEY uk_val(val));

-- 10萬次 REPLACE
-- 10萬次 INSERT ... ON DUPLICATE KEY UPDATE
-- 後者通常快 30-50%，且無自增 ID 空洞

避坑提醒

• REPLACE 要求表有 PRIMARY KEY 或 UNIQUE NOT NULL 索引，否則退化為普通 INSERT。
• ON DUPLICATE KEY UPDATE 同樣依賴唯一約束衝突觸發。
• 並發高時注意死鎖風險：按固定順序訪問表、縮短事務、考慮 INSERT IGNORE + 後續 UPDATE 分離。

總結

90% 場景首選 INSERT ... ON DUPLICATE KEY UPDATE：ID 穩定、觸發器正確、部分更新靈活、性能更優。REPLACE INTO 視為歷史兼容產物，新代碼慎用。