萬盛學電腦網

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

深度解析 MySQL "ON DUPLICATE KEY UPDATE" 語法:單行、多行、VALUES() 函數與實戰陷阱

語法核心

INSERT INTO table (columns) VALUES (...)
ON DUPLICATE KEY UPDATE col = expression;
  • 觸發條件:插入導致 主鍵 或 唯一索引 衝突。
  • 效果:衝突行執行 UPDATE,否則 INSERT。
  • 受影響行數:INSERT=1,UPDATE=2。

基礎示例

-- a 為 PK/UNIQUE,已存在 a=1
INSERT INTO t (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c = c + 1;
-- 等價於
UPDATE t SET c = c + 1 WHERE a = 1;

多行插入與 VALUES() 函數

場景:批量 upsert,需引用「本次插入值」而非「舊值」

INSERT INTO t (a, b, c) VALUES
(1, 2, 3),
(2, 5, 7),
(3, 3, 6),
(4, 8, 2)
ON DUPLICATE KEY UPDATE b = VALUES(b);

假設表已有 (2,2,9)、(3,2,1):

  • a=2 衝突 → b 更新為插入值 5(而非 c+1)
  • a=3 衝突 → b 更新為插入值 3
  • 新增 a=1、a=4

關鍵點: VALUES(col) 只在 ON DUPLICATE KEY UPDATE 子句中有效,引用的是 VALUES 列表中對應列的值。

對比:c=c+1 vs c=VALUES(c)

-- 同樣插入 (1,3),(1,7) 衝突 a=1
INSERT INTO t (a, c) VALUES (1, 3), (1, 7) ON DUPLICATE KEY UPDATE c = c + 1;
-- 結果 c = 原值 + 1 + 1 = 原值 + 2(執行兩次 UPDATE)

INSERT INTO t (a, c) VALUES (1, 3), (1, 7) ON DUPLICATE KEY UPDATE c = VALUES(c);
-- 結果 c = 7(取最後一條插入值)

實戰陷阱與最佳實踐

  1. 多唯一鍵表慎用:衝突任一唯一鍵即觸發更新,且僅更新第一匹配行,易導致數據不一致。
  2. 自增 ID 不變:UPDATE 不改變自增列,REPLACE INTO 會重新生成。
  3. 觸發器:觸發 BEFORE/AFTER UPDATE,不觸發 INSERT 觸發器(除非無衝突)。
  4. 複製一致性:Statement Based Replication (SBR) 下,主備唯一鍵定義必須一致,否則備庫可能報錯或數據偏移。建議 Row Based Replication (RBR)。
  5. 性能:比「SELECT 判斷 + INSERT/UPDATE」少一次網絡往返,無競態條件,推薦高併發 upsert 場景。

完整實戰模板

INSERT INTO `metrics` (`date`, `dimension`, `count`, `updated_at`)
VALUES
  ('2024-01-15', 'page_view', 1, NOW()),
  ('2024-01-15', 'click', 1, NOW())
ON DUPLICATE KEY UPDATE
  `count` = `count` + VALUES(`count`),
  `updated_at` = VALUES(`updated_at`);

適用於:日誌聚合、計數器、狀態同步、冪寫入口。

copyright © 萬盛學電腦網 all rights reserved