前言

高併發系統中，「判斷存在則更新，不存在則插入」是高頻需求。傳統「先 SELECT 再 INSERT/UPDATE」存在競態條件，且雙倍 RTT。MySQL 提供原子級解決方案：**唯一索引 + INSERT ... ON DUPLICATE KEY UPDATE**，單條語句完成冪等寫入，保證主鍵 ID 穩定、Binlog 幂等、觸發器語義正確。本文深度解析原理、語法變體、性能特徵、常見陷阱與生產級最佳實踐。

一、核心機制

1. 唯一約束觸發：InnoDB 執行 INSERT 時，檢測主鍵或唯一索引衝突
2. 轉為更新：捕獲 ER_DUP_ENTRY (1062)，改為執行 UPDATE 子句
3. 原子性：整個過程在單一事務、單一行鎖內完成，無併發窗口
4. 返回值：1=插入新行，2=更新現有行，0=無變化 (值相同)

二、標準語法與變體

1. 完整列表形式

INSERT INTO `device_token` (`user_id`, `device_id`, `token`, `online`, `updated_at`)
VALUES (2479031, 'dev_abc', 'tok_xyz', 1, NOW())
ON DUPLICATE KEY UPDATE
  `token` = VALUES(`token`),
  `online` = VALUES(`online`),
  `updated_at` = VALUES(`updated_at`);

2. VALUES() 函數引用插入值

VALUES(col) 返回 VALUES 子句中該列的值，避免重複書寫。

3. 表達式更新 (計數器、時間戳)

INSERT INTO `user_stats` (`uid`, `login_cnt`, `last_login`)
VALUES (1001, 1, NOW())
ON DUPLICATE KEY UPDATE
  `login_cnt` = `login_cnt` + 1,
  `last_login` = VALUES(`last_login`);

4. 批量冪等寫入 (單語句多行)

INSERT INTO `user_stats` (`uid`, `login_cnt`, `last_login`) VALUES
  (1001, 1, NOW()),
  (1002, 1, NOW()),
  (1003, 1, NOW())
ON DUPLICATE KEY UPDATE
  `login_cnt` = `login_cnt` + VALUES(`login_cnt`),
  `last_login` = VALUES(`last_login`);
-- 每行獨立判斷衝突並更新

5. SELECT 來源形式

INSERT INTO `target` (`id`, `data`)
SELECT `id`, `data` FROM `staging`
ON DUPLICATE KEY UPDATE `data` = VALUES(`data`);

三、對比：為什麼不是 REPLACE / IGNORE？

特性	ON DUPLICATE KEY UPDATE	REPLACE INTO	INSERT IGNORE
主鍵 ID 保持	✅ 是	❌ 新 ID	✅ 是
外鍵安全	✅ 安全	❌ 斷裂	✅ 安全
部分列更新	✅ 靈活	❌ 全行覆蓋	❌ 不更新
觸發器	UPDATE 觸發器	DELETE+INSERT	不觸發
Binlog 事件	UPDATE (ROW模式幂等)	DELETE+INSERT	不記錄/IGNORE
自增 ID 消耗	無額外消耗	每次消耗	衝突時消耗
適用場景	95% Upsert	全行覆蓋無外鍵	純去重、不更新

四、生產級最佳實踐

1. 表結構設計

CREATE TABLE `device_token` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '業務主鍵',
  `device_id` VARCHAR(64) NOT NULL,
  `token` VARCHAR(512) NOT NULL,
  `online` TINYINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_device` (`user_id`, `device_id`)  -- 組合唯一索引觸發 ODKU
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 應用層封裝 (Java 示例)

@Repository
public interface DeviceTokenMapper {
  @Insert("""
    INSERT INTO device_token (user_id, device_id, token, online, updated_at)
    VALUES (#{userId}, #{deviceId}, #{token}, #{online}, NOW())
    ON DUPLICATE KEY UPDATE
      token = VALUES(token),
      online = VALUES(online),
      updated_at = VALUES(updated_at)
  """)
  int upsert(DeviceTokenDTO dto);  // 返回 1/2/0
}

3. 監控指標

• INSERT ... ON DUPLICATE KEY UPDATE 執行次數 (Com_insert + Com_update)
• 返回值分布：Insert(1) vs Update(2) 比例，判斷新增/活躍用戶比
• 行鎖等待時間：performance_schema.events_waits_summary_global_by_event_name 監控 wait/lock/table/sql/handler

五、常見坑點與避雷

1. 唯一索引缺失：無 UK 則無法觸發 ODKU，退化為普通 INSERT，報 Duplicate Entry 錯誤
2. VALUES() 函數誤用：VALUES(col) 僅在 ODKU 子句中有效，其他位置返回 NULL
3. 自增計數器洩漏：5.7 之前 innodb_autoinc_lock_mode=1 時，ODKU 衝突也會消耗自增值 (8.0 修復)
4. 觸發器遞歸：UPDATE 觸發器中再次操作同表可能導致死循環
5. 分區表限制：分區表上 ODKU 需分區鍵包含在唯一索引中
6. 外鍵檢查順序：ODKU 先檢查唯一鍵再檢查外鍵，外鍵失敗不會觸發更新邏輯

六、擴展：INSERT IGNORE + 應用層補償 (極高併發寫入優化)

極端寫入熱點 (如秒殺) 下，行鎖競爭導致 ODKU 吞吐下降。可採用：

-- 1. 嘗試插入 (極快，無鎖競爭)
INSERT IGNORE INTO `seckill_stock` (`goods_id`, `stock`) VALUES (1001, 10000);

-- 2. 異步/定時任務補償更新 (非實時一致性可接受)
UPDATE `seckill_stock` SET `stock` = 10000 WHERE `goods_id` = 1001 AND `stock` != 10000;

適用：庫存預扣、計數器近似、最終一致性業務。

七、總結

唯一索引 + ON DUPLICATE KEY UPDATE 是 MySQL 原生、高性能、語義正確的冪等寫入方案。掌握其底層行為 (鎖、觸發器、Binlog、自增 ID)、熟練運用 VALUES()、批量語法、表達式更新，並建立監控與避坑清單，是後端工程師處理高併發寫入的核心技能。