MySQL REPLACE 與 INSERT ... ON DUPLICATE KEY UPDATE 深度對比與實戰應用

業務場景：排序字段重排序

電影表 movie(id PK, orderby)，初始 id=1..10 對應 orderby=1..10。需將 id=9 移至第 3 位，原 3-8 位順延 (orderby+1)。單條 SQL 解決，避免循環多次更新。

方案一：REPLACE INTO

REPLACE INTO movie (id, orderby) VALUES
(9, 3), (3, 4), (4, 5), (5, 6), (6, 7), (7, 8), (8, 9);

機制：

1. 嘗試插入，若主鍵/唯一鍵衝突 → **刪除舊行** → **插入新行**
2. 返回影響行數：1=純插入，2=刪除+插入，>2=多唯一鍵觸發多行刪插

限制：

• 表**必須有主鍵或唯一非空索引**
• **觸發 DELETE + INSERT**，會激活觸發器、外鍵級聯、自增 ID 重置 (若主鍵自增)
• 所有列按新值覆蓋，**無法保留舊列值** (除非在 VALUES 中帶上)

方案二：INSERT ... ON DUPLICATE KEY UPDATE (推薦)

INSERT INTO movie (id, orderby) VALUES
(9, 3), (3, 4), (4, 5), (5, 6), (6, 7), (7, 8), (8, 9)
ON DUPLICATE KEY UPDATE `orderby` = VALUES(`orderby`);

機制：

1. 嘗試插入，若主鍵/唯一鍵衝突 → **更新指定列** (此處更新 orderby)
2. 返回影響行數：1=新插入，2=更新現有行
3. VALUES(col) 取自 VALUES 子句中該列的值

優勢：

• **原地更新**，不刪除行，**不觸發 DELETE 觸發器、不影響外鍵、不重置自增 ID**
• 可**選擇性更新部分列**，保留其他列舊值
• 支援多行批量操作，效能極高
• 可引用舊值：UPDATE count = count + 1 或 UPDATE count = VALUES(count) + 1

核心差異對比表

進階用法：VALUES() 函數與多行批量

-- 批量插入/更新，統計點擊數
INSERT INTO stats (date, page, clicks) VALUES
('2024-01-01', '/home', 1),
('2024-01-01', '/about', 1)
ON DUPLICATE KEY UPDATE clicks = clicks + VALUES(clicks);
-- 衝突時 clicks = 原值 + 1 (VALUES(clicks)=1)

注意： VALUES(col) 僅在 ON DUPLICATE KEY UPDATE 子句中有效，其他場合返回 NULL。

避坑指南

1. 多唯一鍵表慎用： 若表有多個唯一索引，衝突可能匹配多行，**僅更新第一行**，行為不可預期。建議**單一主鍵/唯一鍵表使用**
2. DELAYED 無效： INSERT DELAYED ... ON DUPLICATE KEY UPDATE 中 DELAYED 會被忽略
3. 主鍵非自增時： REPLACE 會嘗試插入指定 PK，衝突則刪除舊行插入新行，效果同 UPDATE 但開銷大
4. 效能： 批量操作 (千行級) 效能遠優於循環單條執行，但單次別超 max_allowed_packet

實戰總結：排序重排序最佳實踐

-- 將 @target_id 移至 @new_pos，區間 [@new_pos, @old_pos) 順延
-- 假設 @target_id=9, @old_pos=9, @new_pos=3
INSERT INTO movie (id, orderby) VALUES
(9, 3), (3, 4), (4, 5), (5, 6), (6, 7), (7, 8), (8, 9)
ON DUPLICATE KEY UPDATE orderby = VALUES(orderby);

**單條 SQL、原子操作、無副作用、效能最優** — 這是處理「移動排序」類問題的標準解法。