適用場景

「若記錄存在則更新，否則插入」（Upsert）。常見於：計數器增量、用戶設定寫入、日誌去重寫入。

語法結構

INSERT INTO table (cols) VALUES (...)
ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), ...;

• 觸發條件：唯一鍵衝突（PRIMARY KEY 或 UNIQUE INDEX）。
• VALUES(col) 取得 VALUES 子句中該欄位的輸入值。

實戰範例

表結構

CREATE TABLE spk_goods (
  gid INT AUTO_INCREMENT PRIMARY KEY,
  cid INT NOT NULL COMMENT '分類',
  name CHAR(30) NOT NULL COMMENT '貨物名稱',
  UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

現有資料

gid | cid | name
----|-----|-----
1   | 1   | apple
2   | 2   | banana

案例一：插入已存在 name='apple' → 更新 name 加後綴

INSERT INTO spk_goods (cid, name) VALUES (1, 'apple')
ON DUPLICATE KEY UPDATE name = CONCAT(name, 'new');

結果：apple → applenew，gid=1 不變。

案例二：插入新 name='watermelon' → 新增行

INSERT INTO spk_goods (cid, name) VALUES (2, 'watermelon')
ON DUPLICATE KEY UPDATE name = CONCAT(name, 'new');

結果：新增 gid=3, cid=2, name='watermelon'。

批次 Upsert 範例

INSERT INTO t (a,b,c) VALUES
  (1,2,3), (2,5,7), (3,3,6), (4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

• 假設 a 為 UNIQUE，原有 (2,2,9)、(3,2,1)。
• 結果：(2,5,9)、(3,3,1) 更新；(1,2,3)、(4,8,2) 新增。

進階技巧

使用 LAST_INSERT_ID() 取得自增 ID

INSERT INTO t (id, val) VALUES (NULL, 'x')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), val='x';
SELECT LAST_INSERT_ID(); -- 返回新增或更新行的自增 ID

條件更新

INSERT INTO t (k, v) VALUES ('key', 100)
ON DUPLICATE KEY UPDATE v = IF(VALUES(v) > v, VALUES(v), v); -- 只增不減

效能與注意事項

• 避免死鎖：高並發下多事務同時 Upsert 同一鍵值，易產生死鎖 → 捕獲 ER_LOCK_DEADLOCK 重試。
• 觸發器：BEFORE INSERT 與 BEFORE UPDATE 皆會觸發。
• 複製：Row-based replication 正確重放；Statement-based 可能主從不一致（建議 binlog_format=ROW）。
• 自增間隙：插入衝突導致自增值跳過，屬正常現象（InnoDB 自增鎖機制）。

對比：REPLACE INTO

首選 ON DUPLICATE KEY UPDATE，除非確確實實需要「完全替換行」。

特性	ON DUPLICATE KEY UPDATE	REPLACE INTO
操作本質	更新現有行	DELETE + INSERT
自增 ID	保持不變	重新分配
外鍵關聯	保留	斷開（舊行刪除）
觸發器	UPDATE 類	DELETE + INSERT 類