萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ON DUPLICATE KEY UPDATE 深度解析:VALUES() 函式實現多行衝突時精準更新、批量寫入最佳實踐

語法與核心邏輯

INSERT INTO t (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c = c + 1;
  • a 為 PRIMARY KEY 或 UNIQUE KEY。
  • 若 a=1 不存在 → 插入新行,受影響行數 = 1。
  • 若 a=1 已存在 → 更新該行 c = c + 1,受影響行數 = 2。

關鍵進階:VALUES(col) 取「本次插入值」而非「舊值」

INSERT INTO t (a, c) VALUES (1, 3), (1, 7) ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 執行後 c = 7(取第二組插入值 7,而非 c+1 遞增)
  • VALUES(c) 返回**當前 INSERT 列表中對應行的 c 值**。
  • 多行 INSERT 時,每行衝突各自取自己攜帶的值更新,互不干擾。

批量「不存在插入、存在更新」標準模板

INSERT INTO target (pk, col1, col2, updated_at)
VALUES 
  (1, 'a', 'x', NOW()),
  (2, 'b', 'y', NOW()),
  (3, 'c', 'z', NOW())
ON DUPLICATE KEY UPDATE
  col1 = VALUES(col1),
  col2 = VALUES(col2),
  updated_at = VALUES(updated_at);
  • 單條 SQL 完成 UPSERT(Update + Insert)。
  • 原子性:要麼全成功,要麼全回滾(InnoDB)。
  • 避免應用層 SELECT → IF EXISTS UPDATE ELSE INSERT 競態條件。

受影響行數語義

回傳值含義
1純插入 1 行
2更新 1 行(舊行標記刪除 + 新行插入)
>2多行衝突,每衝突 1 行計 2

應用層可據此判斷「是新增還是更新」。

多唯一索引場景

CREATE TABLE t (a INT UNIQUE, b INT UNIQUE, c INT);
INSERT INTO t VALUES (1,1,1), (2,2,2), (3,3,3);
-- a,b 皆為 UNIQUE

INSERT INTO t (a,b,c) VALUES (1,2,9) ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 衝突:a=1 與第1行衝突,b=2 與第2行衝突
-- 結果:第1行 c→9,第2行 c→9,插入新行 (1,2,9)?實測:刪除衝突行、插入新行,最終表僅剩 (1,2,9) 一行!

警告:多唯一索引時,單行插入可能同時衝突多行,導致**多行被刪除、僅保留新行**,資料量劇減。生產環境**極度不建議**多唯一鍵配合 ON DUPLICATE KEY UPDATE。

效能與最佳實踐

  • 批次大小 1000–5000 行,避免單條 SQL 過大(max_allowed_packet)。
  • 配合 LOAD DATA ... ON DUPLICATE KEY UPDATE(MySQL 8.0.20+)更快。
  • 唯一鍵衝突率高時,優化器可能退化為全表掃描,監控 Handler_read_rnd_next。
  • 只用於單一主鍵/唯一鍵表,多唯一鍵請改用應用層邏輯或 REPLACE INTO(同樣有多行刪除風險)。

總結

ON DUPLICATE KEY UPDATE + VALUES(col) 是 MySQL 高效 UPSERT 核心技巧:**單句完成、原子性、取插入值更新、批量友好**。牢記「多唯一索引會刪除多行」陷阱,生產環境嚴格限制為單一主鍵/唯一鍵場景使用。

copyright © 萬盛學電腦網 all rights reserved