您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL INSERT ... ON DUPLICATE KEY UPDATE 語法深度解析

編輯：MySQL 綜合教學

`INSERT ... ON DUPLICATE KEY UPDATE` (ODKU) 是 MySQL 獨有的「Upsert」語法，能在主鍵/唯一鍵衝突時自動轉為更新，極大簡化「不存在則插入，存在則更新」的業務邏輯。本文系統解析語法細節、進階用法、效能特性與避坑指南。

基礎語法

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)
ON DUPLICATE KEY UPDATE
  col2 = VALUES(col2),
  col3 = col3 + 1;

觸發條件

表必須有 **主鍵 (PRIMARY KEY)** 或 **唯一索引 (UNIQUE INDEX)**。
插入的資料與現有記錄在 **任一** 唯一鍵上衝突，即觸發 UPDATE 分支。
若有多個唯一鍵同時衝突，**只更新第一條匹配記錄** (類似 `LIMIT 1`)，建議避免多唯一鍵表使用 ODKU。

核心函數：VALUES(col)

在 `UPDATE` 子句中，`VALUES(col_name)` 返回 **原本準備插入的值**，而非資料庫現有值。這是構建「插入值參與更新運算」的關鍵。

單行插入範例

-- 統計每日 UV：不存在插入 1，存在則 +1
INSERT INTO daily_uv (date, uv) VALUES ('2024-01-15', 1)
ON DUPLICATE KEY UPDATE uv = uv + 1;

-- 等價於 (但需兩次查詢)
INSERT INTO daily_uv (date, uv) VALUES ('2024-01-15', 1)
ON DUPLICATE KEY UPDATE uv = VALUES(uv) + 1;  -- VALUES(uv)=1

批次插入威力：單條 SQL 完成多行 Upsert

INSERT INTO user_stats (user_id, login_count, last_login)
VALUES
  (1001, 1, '2024-01-15 08:30:00'),
  (1002, 1, '2024-01-15 09:15:00'),
  (1003, 1, '2024-01-15 10:00:00')
ON DUPLICATE KEY UPDATE
  login_count = login_count + VALUES(login_count),  -- 累加
  last_login = VALUES(last_login);                  -- 覆蓋為最新時間

**效能優勢**：相比應用層 `SELECT` 判斷再 `INSERT`/`UPDATE`，ODKU 減少網路往返、鎖競爭窗口、解析優化開銷，批次時效能提升 **10x-100x**。

進階技巧

1. 利用 VALUES() 構建複雜更新邏輯

INSERT INTO products (sku, price, stock, version)
VALUES ('SKU001', 99.00, 10, 1)
ON DUPLICATE KEY UPDATE
  price = VALUES(price),
  stock = stock + VALUES(stock),
  version = version + 1;

2. 配合自增 ID 獲取 ID (LAST_INSERT_ID)

INSERT INTO logs (msg, created_at) VALUES ('test', NOW())
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), msg = VALUES(msg);

SELECT LAST_INSERT_ID(); -- 返回插入時的自增 ID，或更新時該行的 ID

**關鍵**：`ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)` 讓更新分支也能讓 `LAST_INSERT_ID()` 返回該行 ID，否則更新時返回 0。

3. 忽略特定衝突 (INSERT IGNORE vs ODKU)

語法	衝突行為	返回值	適用場景
INSERT IGNORE	靜默忽略，不插入、不更新、不報錯	affected_rows=0	冪寫入、去重、不關心現有值
ON DUPLICATE KEY UPDATE	執行更新	插入=1，更新=2	需更新計數器、時間戳、版本號
REPLACE INTO	刪除舊行 + 插入新行	刪除+插入=2	極少用，會觸發 FK 級聯刪除、自增 ID 變更、觸發器雙重觸發

效能與鎖行為

行鎖：ODKU 在唯一鍵上加 **排他鎖 (X-lock)**，衝突時持有到事務結束。
死鎖風險：高併發批次 ODKU 若值順序不一致，極易死鎖。**解法**：應用層按唯一鍵值排序後再批次插入。
複製延遲：主庫一條 ODKU，從庫需執行相同邏輯。大批次 ODKU 可能導致主從延遲，建議拆分小批次 (500-1000 行/批)。
自增 ID 燒盡：每次 ODKU (即使更新) 都會消耗一個自增 ID 值 (InnoDB 預分鎖機制)。極高頻 Upsert 可能導致 BIGINT 耗盡 (理論上極難)，但會造成自增 ID 不連續。

避坑清單

❌ **無唯一鍵表使用 ODKU** → 全表掃描 + 鎖全表，災難性效能。
❌ **多唯一鍵表使用 ODKU** → 衝突行不確定，業務邏輯錯誤。
❌ **UPDATE 子句引用其他表/子查詢** → 不支援，只能引用 `VALUES(col)`、`col` (現值)、常量、函數。
❌ **大批次未分頁** → 長事務、鎖升級、複製延遲、Binlog 膨脹。
❌ **配合 `DELAYED` 選項** → `INSERT DELAYED ... ON DUPLICATE KEY UPDATE` 語法錯誤，`DELAYED` 被忽略。
✅ **批次前按唯一鍵排序** → 避免死鎖。
✅ **監控 `Innodb_deadlocks`、`Com_insert_on_duplicate`** 狀態變數。
✅ **測試驗證 `affected_rows` 返回值** (1=插入, 2=更新) 供業務邏輯判斷。

對比：標準 SQL MERGE (MySQL 8.0.20+ 不支援)

標準 SQL `MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...` 功能更強 (支援多表、複雜條件)，但 MySQL 至今未實作。ODKU 是 MySQL 的輕量替代方案，語法簡潔、效能高，覆蓋 95% Upsert 場景。

實戰模板：冪等寫入通用函數 (偽代碼)

function upsertBatch(table, uniqueKeys, rows, updateCols) {
  // rows: [{col1:v, col2:v...}, ...]
  // uniqueKeys: ['uk1', 'uk2'] 用於應用層排序
  // updateCols: 需更新的欄位 (含累加邏輯)
  
  rows.sort((a,b) => compareByKeys(a,b,uniqueKeys)); // 關鍵：避免死鎖
  
  const chunks = chunk(rows, 500);
  for (const batch of chunks) {
    const cols = Object.keys(batch[0]);
    const placeholders = batch.map(() => '(' + cols.map(() => '?').join(',') + ')').join(',');
    const updates = updateCols.map(c => `${c} = ${c} + VALUES(${c})`).join(', ');
    
    const sql = `INSERT INTO ${table} (${cols.join(',')}) VALUES ${placeholders} ON DUPLICATE KEY UPDATE ${updates}`;
    const params = batch.flatMap(r => cols.map(c => r[c]));
    await db.execute(sql, params);
  }
}

`ON DUPLICATE KEY UPDATE` 是 MySQL 開發者工具箱裡的「瑞士軍刀」。掌握其語法細節、鎖行為、批次優化與避坑指南，能讓高併發寫入邏輯既簡潔又強健。