MySQL UPSERT 模式：INSERT ... ON DUPLICATE KEY UPDATE 實戰語法、VALUES()函數與多唯一索引陷阱

INSERT ... ON DUPLICATE KEY UPDATE (ODKU) 是 MySQL 實現「有則更新、無則插入」的原子操作，廣泛用於計數器、同步任務、冪等寫入。本文聚焦核心語法、VALUES() 函數妙用、多唯一索引行為邊界與生產級優化模式。

一、 核心語法與語義

INSERT INTO table (pk, col1, col2) VALUES (v_pk, v1, v2)
ON DUPLICATE KEY UPDATE col1 = v1_new, col2 = v2_new;

• 衝突判定：主鍵 或 任一唯一索引 重複。
• 執行動作：衝突 → 執行 UPDATE 子句；不衝突 → 普通 INSERT。
• 返回值：1 新插入，2 更新現有，0 (極罕見 IGNORE 組合)。

二、 VALUES() 函數：引用「本來要插入的值」

在 UPDATE 子句中，VALUES(col_name) 返回該行 VALUES 列表中對應的值，而非表中舊值。

單行示例

INSERT INTO counters (date, type, cnt) VALUES ('2024-01-15', 'pv', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);  -- cnt = cnt + 1

多行批量 Upsert (高效計數器聚合)

INSERT INTO daily_stats (stat_date, metric, val) VALUES
  ('2024-01-15', 'pv', 100),
  ('2024-01-15', 'uv', 50),
  ('2024-01-15', 'order', 10)
ON DUPLICATE KEY UPDATE val = val + VALUES(val);

等效於分別執行：

UPDATE daily_stats SET val = val + 100 WHERE stat_date='2024-01-15' AND metric='pv';
UPDATE daily_stats SET val = val + 50  WHERE stat_date='2024-01-15' AND metric='uv';
UPDATE daily_stats SET val = val + 10  WHERE stat_date='2024-01-15' AND metric='order';

單語句、單次網絡往返、原子性 —— 高併發計數器首選。

三、 多唯一索引陷阱 (必讀)

場景

CREATE TABLE t (
  a INT PRIMARY KEY,
  b INT UNIQUE,
  c INT
);

問題

INSERT INTO t (a,b,c) VALUES (1, 10, 100)
ON DUPLICATE KEY UPDATE c = c + 1;

• 若表中已有 a=1 → 更新該行 c+1 (預期內)。
• 若表中無 a=1 但有 b=10 → 更新 b=10 那行，a 保持原值，c+1。
• 若同時存在 a=1 和 b=10 (不同行) → 只更新第一個匹配到的行 (非確定性，取決於優化器訪問順序)。

規避原則

1. 業務上保證：參與 ODKU 的唯一鍵同一時刻最多命中一行。
2. 設計上收斂：單表僅保留一個業務唯一鍵 (PK 或 UK)，其他唯一性靠應用層/分佈式鎖保證。
3. 或改用 REPLACE INTO：但 REPLACE 是 DELETE + INSERT，會導致自增 ID 變化、外鍵級聯、觸發器雙重觸發，一般不推薦。

四、 與 IGNORE、DELAYED 組合行為

五、 生產優化模式

1. 覆蓋索引消除回表

-- 唯一鍵 (date, metric) + 更新列 val
ALTER TABLE daily_stats ADD UNIQUE uk_date_metric (stat_date, metric);
-- ODKU 可直接在索引頁完成更新，無需聚簇索引回表

2. 批量大小控制

• 單條 100-500 行，超過 max_allowed_packet (默認 4MB/64MB) 會報錯。
• 大批量拆分為多條，事務包裹保證原子性 (或接受部分成功)。

3. 監指標：affected_rows 語義

# 應用層解析
if affected == 1: metrics.counter('upsert.insert')
elif affected == 2: metrics.counter('upsert.update')

4. 避免觸發器副作用

ODKU 觸發 BEFORE UPDATE / AFTER UPDATE。若觸發器內再操作同表，可能死鎖/遞歸。建議：核心計數器表不掛觸發器，異步通道 (Binlog/CDC) 處理下游。

六、 MySQL 8.0+ 替代方案：INSERT ... ON CONFLICT (不支持)

MySQL 尚未支持標準 SQL ON CONFLICT DO UPDATE (PostgreSQL/SQLite 語法)。ODKU 仍是唯一原生 Upsert 方式。

七、 總結

ODKU = 原子 Upsert + 批量計數器聚合 + VALUES() 引用插入值。掌握 VALUES(col)、多唯一索引非確定性、組合互斥規則、覆蓋索引優化，即可在高併發寫入鏈路中寫出正確、高效、可觀測的冪等邏輯。生產原則：單表單一業務唯一鍵 + 批量 ODKU + 異步下游。