MySQL 唯一索引配合 ON DUPLICATE KEY UPDATE 實現「不存在插入、存在更新」

業務常見需求：寫入資料時，若主鍵/唯一鍵衝突則更新，否則插入新行。MySQL 提供 INSERT ... ON DUPLICATE KEY UPDATE 語法，配合唯一索引即可單條 SQL 完成「Upsert」邏輯，避免「先查再寫」的競態與效能損耗。

核心語法

INSERT INTO `table` (id, user_id, token, online)
VALUES (NULL, '2479031', '232', 1)
ON DUPLICATE KEY UPDATE
  user_id = VALUES(user_id),
  token   = VALUES(token),
  online  = VALUES(online);

前置條件：必須有唯一索引

-- 單欄唯一索引
ALTER TABLE `table_name` ADD UNIQUE (user_id);

-- 組合唯一索引
ALTER TABLE `table_name` ADD UNIQUE (user_id, device_id);

無唯一索引，ON DUPLICATE KEY UPDATE 永不觸發，視同普通 INSERT。

實戰場景：App 端上報用戶在線狀態

介面：GET /report?user_id=2479031&device=xxx&online=1
邏輯：用戶每次開啟/關閉 App 呼叫，以 user_id 為唯一鍵，存在則更新 token、online，不存在則新增。

執行流程解析

1. 嘗試插入 user_id='2479031'
2. 若唯一鍵衝突 → 執行 UPDATE 部分，相當於：

   UPDATE `table`
   SET user_id='2479031', token='232', online=1
   WHERE user_id='2479031';

3. 若無衝突 → 正常插入新行

進階技巧

• VALUES(col) 引用插入值，避免硬編碼
• 可搭配 IF()、CASE WHEN 做條件更新
• 大批量 Upsert 時，考慮 LOAD DATA ... ON DUPLICATE KEY UPDATE 或分批提交

避坑清單

1. 確認目標欄位已建立 UNIQUE INDEX 或 PRIMARY KEY
2. InnoDB 下，唯一鍵衝突會加 行鎖，高併發注意死鎖風險
3. AUTO_INCREMENT 主鍵在衝突更新時仍會消耗自增值（已知行為，5.7+ 可用 innodb_autoinc_lock_mode=2 緩解）

掌握此模式，單表 Upsert 不再需要應用層邏輯判斷，簡潔且原子化。