MySQL INSERT ... ON DUPLICATE KEY UPDATE 語法詳解：判斷存在則更新、不存在則插入

INSERT ... ON DUPLICATE KEY UPDATE (ODKU) 是 MySQL 特有語法，實現「主鍵/唯一鍵衝突時更新，否則插入」的原子操作，適用於計數器、上傳狀態、配置項等場景。

核心語法

INSERT INTO tbl (a, c) VALUES (1, 3)
ON DUPLICATE KEY UPDATE c = c + 1;

等價邏輯

若 a 為 PK/UK 且值 1 已存在：

UPDATE tbl SET c = c + 1 WHERE a = 1;

若不存在：

INSERT INTO tbl (a, c) VALUES (1, 3);

影響行數返回值

• 1：新插入一行
• 2：更新既有行
• 0：更新但值未實際改變

多行插入與 VALUES() 函數

-- 多行插入，衝突時 c 遞增
INSERT INTO tbl (a, c) VALUES (1, 3), (1, 7)
ON DUPLICATE KEY UPDATE c = c + 1;
-- 結果：c = 4 (第一行 3+1，第二行衝突再 +1)

-- 衝突時取「當行插入值」覆蓋
INSERT INTO tbl (a, c) VALUES (1, 3), (1, 7)
ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 結果：c = 7 (第二行的 7 覆蓋)

適用場景

• 計數器原子遞增：ON DUPLICATE KEY UPDATE cnt = cnt + 1
• 配置項寫入：ON DUPLICATE KEY UPDATE val = VALUES(val)
• 資料同步/合併：避免 SELECT ... IF EXISTS UPDATE ELSE INSERT 競態條件

限制與注意

• 僅 MySQL 支援，非標準 SQL（PostgreSQL 用 ON CONFLICT，SQL Server 用 MERGE）
• 需表有 **PRIMARY KEY 或 UNIQUE KEY** 觸發重複檢測
• 多重唯一鍵衝突時，**僅匹配第一個** 唯一鍵衝突的行
• 觸發 BEFORE INSERT/UPDATE 觸發器，**不觸發 AFTER** 觸發器（5.7+ 行為可能不同）

官方文檔

MySQL 5.1 Reference Manual - INSERT Syntax

ODKU 是 MySQL 高併發寫入的「瑞士軍刀」，善用它簡化應用層邏輯並保證原子性。