語法對照

-- REPLACE INTO
REPLACE [INTO] tbl_name [(cols)] VALUES (vals),...;
REPLACE [INTO] tbl_name SET col=val,...;
REPLACE [INTO] tbl_name [(cols)] SELECT ...;

-- INSERT ... ON DUPLICATE KEY UPDATE (IODKU)
INSERT INTO tbl_name [(cols)] VALUES (vals),...
ON DUPLICATE KEY UPDATE col=VALUES(col),...;

核心機制差異

特性	REPLACE INTO	INSERT ... ON DUPLICATE KEY UPDATE
衝突處理	**刪除舊行 → 插入新行**	**更新舊行** (原地修改)
觸發條件	PRIMARY KEY 或 UNIQUE KEY 衝突	PRIMARY KEY 或 UNIQUE KEY 衝突
自增 ID	**會改變** (刪除重插產生新 ID)	**保持不變**
未指定欄位	**重置為默認值/NULL**	**保持原值**
外鍵關聯	觸發 ON DELETE CASCADE → 級聯刪除關聯行	不觸發刪除，僅更新
觸發器	觸發 BEFORE/AFTER DELETE + INSERT	觸發 BEFORE/AFTER UPDATE
回傳值	影響行數 = 刪除行數 + 插入行數 (1 或 >1)	1=新增, 2=更新, 0=無變化
權限需求	INSERT + DELETE	INSERT + UPDATE

實戰場景選型

場景 A：日誌/審計表，需保留歷史 ID

-- 必須用 IODKU，保持 ID 不變
INSERT INTO audit_log (id, action, time) VALUES (1001, 'login', NOW())
ON DUPLICATE KEY UPDATE action=VALUES(action), time=VALUES(time);

場景 B：配置表，全量覆蓋、不在乎 ID

-- REPLACE 簡潔
REPLACE INTO config (k, v, updated) VALUES ('site_name', 'MySite', NOW());

場景 C：計數器/統計表，原子增減

-- 僅 IODKU 支援增量更新
INSERT INTO counter (date, cnt) VALUES ('2024-01-15', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

場景 D：含外鍵的子表

-- REPLACE 會觸發級聯刪除，極其危險
-- 必須用 IODKU
INSERT INTO order_item (order_id, sku, qty) VALUES (100, 'A001', 5)
ON DUPLICATE KEY UPDATE qty = qty + 5;

效能對比

• 單行衝突：IODKU 略快 (避免刪除+插入雙操作)
• 批量衝突：IODKU 明顯優勢 (減少 undo log、索引維護)
• 無衝突：兩者相近

進階：VALUES() 函數與別名

INSERT INTO t (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE 
  b = VALUES(b),           -- 引用插入值
  c = c + VALUES(c),       -- 可運算
  a = a;                   -- 保持原值 (無操作)

MySQL 8.0 新特性：INSERT ... ON DUPLICATE KEY UPDATE 別名

INSERT INTO t (a,b) VALUES (1,2) AS new
ON DUPLICATE KEY UPDATE b = new.b;

總結決策樹

1. 表有外鍵被引用？→ 只能用 IODKU
2. 需保持自增 ID 不變？→ 只能用 IODKU
3. 需保持未指定欄位原值？→ 只能用 IODKU
4. 單純全量覆蓋、無外鍵、不在乎 ID？→ REPLACE 更簡潔
5. 計數器/增量更新？→ 只能用 IODKU

注意事項

• 兩者都要求表有 **PRIMARY KEY 或 UNIQUE INDEX**
• 多個唯一索引衝突時，REPLACE 可能刪除多行再插一行
• IODKU 中 VALUES(col) 僅在 INSERT 部分有值時有效
• 分區表、觸發器、外鍵環境下測試驗證行為