您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL ON DUPLICATE KEY UPDATE 深度解析：單行/多行、VALUES()函數、原子化寫入模式

編輯：MySQL 資料庫相關知識

INSERT ... ON DUPLICATE KEY UPDATE（ODKU）是 MySQL 獨有的「Upsert」語法，以**原子操作**實現「不存在插入、存在更新」，避免應用層 SELECT → IF → INSERT/UPDATE 的併發竄改與雙倍開銷。本文深度解析單行/多行行為、VALUES() 函數妙用、效能優勢與避坑指南。

核心語法與行為


INSERT INTO `table` (a, b, c) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE c = c + 1;
-- 等價於（當 a 為主鍵/唯一鍵且值 1 存在時）
UPDATE `table` SET c = c + 1 WHERE a = 1;

影響行數語義：

返回 1：新插入行
返回 2：更新現有行
返回 >2：多個唯一鍵衝突，多行被刪除/更新（極少見）

多行插入時的 VALUES() 函數關鍵機制

場景：批量插入多行，唯一鍵衝突時，**如何引用「當前衝突行的插入值」**？

案例：表 table (a PK, b, c)，現有 (2,2,9)、(3,2,1)


INSERT INTO `table` (a, b, c) VALUES
  (1, 2, 3),   -- 新行
  (2, 5, 7),   -- a=2 衝突
  (3, 3, 6),   -- a=3 衝突
  (4, 8, 2)    -- 新行
ON DUPLICATE KEY UPDATE b = VALUES(b);

執行結果：

a=1：插入 (1,2,3)
a=2：衝突 → 更新 b = VALUES(b) = 5（取衝突行的插入值），c 保留 9 → 變 (2,5,9)
a=3：衝突 → 更新 b = VALUES(b) = 3，c 保留 1 → 變 (3,3,1)
a=4：插入 (4,8,2)

VALUES() vs 直接列引用對比

寫法	含義	結果
`c = c + 1`	在原有值基礎上 +1	遞增計數器
`c = VALUES(c)`	取當前插入行的 c 值覆蓋	全量覆蓋模式
`c = VALUES(c) + 1`	插入值 + 1	混合模式

進階模式：條件化更新


INSERT INTO stats (date, type, cnt) VALUES ('2024-01-01', 'pv', 1)
ON DUPLICATE KEY UPDATE 
  cnt = cnt + VALUES(cnt),
  updated_at = NOW();
-- 同一天同類型 PV 遞增，自動維護更新時間

效能優勢量化

方案	網路往返	SQL解析	事務	併發安全
應用層 SELECT+IF	2-3次	2-3次	需顯式事務	有竄改窗口
REPLACE INTO	1次	1次	隱式	安全但副作用大
INSERT IGNORE	1次	1次	隱式	僅去重不更新
ODKU	1次	1次	隱式	原子安全、靈活更新

避坑指南

唯一鍵前提：表必須有 PRIMARY KEY 或 UNIQUE INDEX，否則退化為普通 INSERT
非標準 SQL：Oracle 用 MERGE，PostgreSQL 用 ON CONFLICT DO UPDATE，SQL Server 用 MERGE，遷移需改寫
觸發器：觸發 BEFORE INSERT、BEFORE UPDATE、AFTER INSERT、AFTER UPDATE 四類
自增 ID：插入新行消耗自增值；更新不消耗。大量衝突導致自增 ID 快速增長（可接受）
外鍵：更新不觸發 ON UPDATE CASCADE（因非主鍵更新），刪除舊行不發生（ODKU 不刪除）
分區表：5.7+ 支援，分區鍵必須包含在唯一鍵中

實戰模板：計數器/統計表原子遞增


CREATE TABLE daily_stats (
  stat_date DATE NOT NULL,
  metric VARCHAR(32) NOT NULL,
  cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, metric)
) ENGINE=InnoDB;

-- 業務調用（極高併發安全）
INSERT INTO daily_stats (stat_date, metric, cnt) VALUES (CURDATE(), 'order_pay', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

ODKU 是 MySQL 高併發寫入的「核武器」，掌握 VALUES() 引用插入值機制，解鎖原子化 Upsert 全場景。