INSERT INTO t (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c = c + 1;
a 為 PRIMARY KEY 或 UNIQUE KEY。a=1 不存在 → 插入新行,受影響行數 = 1。a=1 已存在 → 更新該行 c = c + 1,受影響行數 = 2。INSERT INTO t (a, c) VALUES (1, 3), (1, 7) ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 執行後 c = 7(取第二組插入值 7,而非 c+1 遞增)
VALUES(c) 返回**當前 INSERT 列表中對應行的 c 值**。INSERT INTO target (pk, col1, col2, updated_at)
VALUES
(1, 'a', 'x', NOW()),
(2, 'b', 'y', NOW()),
(3, 'c', 'z', NOW())
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1),
col2 = VALUES(col2),
updated_at = VALUES(updated_at);
SELECT → IF EXISTS UPDATE ELSE INSERT 競態條件。| 回傳值 | 含義 |
|---|---|
| 1 | 純插入 1 行 |
| 2 | 更新 1 行(舊行標記刪除 + 新行插入) |
| >2 | 多行衝突,每衝突 1 行計 2 |
應用層可據此判斷「是新增還是更新」。
CREATE TABLE t (a INT UNIQUE, b INT UNIQUE, c INT);
INSERT INTO t VALUES (1,1,1), (2,2,2), (3,3,3);
-- a,b 皆為 UNIQUE
INSERT INTO t (a,b,c) VALUES (1,2,9) ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 衝突:a=1 與第1行衝突,b=2 與第2行衝突
-- 結果:第1行 c→9,第2行 c→9,插入新行 (1,2,9)?實測:刪除衝突行、插入新行,最終表僅剩 (1,2,9) 一行!
警告:多唯一索引時,單行插入可能同時衝突多行,導致**多行被刪除、僅保留新行**,資料量劇減。生產環境**極度不建議**多唯一鍵配合 ON DUPLICATE KEY UPDATE。
max_allowed_packet)。LOAD DATA ... ON DUPLICATE KEY UPDATE(MySQL 8.0.20+)更快。Handler_read_rnd_next。REPLACE INTO(同樣有多行刪除風險)。ON DUPLICATE KEY UPDATE + VALUES(col) 是 MySQL 高效 UPSERT 核心技巧:**單句完成、原子性、取插入值更新、批量友好**。牢記「多唯一索引會刪除多行」陷阱,生產環境嚴格限制為單一主鍵/唯一鍵場景使用。