萬盛學電腦網

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

MySQL INSERT ... ON DUPLICATE KEY UPDATE:原生 UPSERT 的正確姿勢

什麼是 UPSERT?

UPSERT = UPDATE + INSERT:記錄存在則更新,不存在則插入。MySQL 通過 ON DUPLICATE KEY UPDATE 子句原生支援,單條 SQL 完成原子操作,避免「先查後寫」的競態條件與雙次往返。

核心語法


INSERT INTO `table` (pk_col, col1, col2, ...)
VALUES (val_pk, val1, val2, ...)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2),
    ...;

前提條件: pk_col 必須是 主鍵 或 唯一索引,否則永遠只會 INSERT,不會觸發 UPDATE。

實戰範例

單行 UPSERT


INSERT INTO `clients` (`client_id`, `client_name`, `client_type`)
VALUES (10345, 'IBM', 'advertising')
ON DUPLICATE KEY UPDATE
    `client_name` = VALUES(`client_name`),
    `client_type` = VALUES(`client_type`);

等價於:
IF EXISTS (SELECT 1 FROM clients WHERE client_id=10345) THEN UPDATE ... ELSE INSERT ...
但單條 SQL 原子執行,無鎖競爭。

批量 UPSERT(高效導入/同步)


INSERT INTO `xinhuazidian` (`a`, `b`, `c`)
VALUES
    ('aaa', 'bbb', 'lao8.org'),
    ('aaa2', 'bbb2', 'm.lao8.org')
ON DUPLICATE KEY UPDATE
    `b` = VALUES(`b`),
    `c` = VALUES(`c`);

判斷鍵為 a(唯一鍵),存在則更新 b,c,不存在則插入新行。單條 SQL 處理多行,網絡往返極少,批量同步效能極高。

VALUES(col) 函數詳解

在 ON DUPLICATE KEY UPDATE 子句中,VALUES(col_name) 代表 VALUES 列表中該列的輸入值,而非資料庫舊值。


INSERT INTO `t` (`a`,`b`,`c`) VALUES (1,2,3), (4,5,6)
ON DUPLICATE KEY UPDATE `c` = VALUES(`a`) + VALUES(`b`);
-- 若 a=1 衝突:c = 1+2 = 3
-- 若 a=4 衝突:c = 4+5 = 9
VALUES() 僅在 INSERT...ON DUPLICATE KEY UPDATE 中有意義,其他上下文返回 NULL。

影響行數語義

  • 1:新插入 1 行。
  • 2:更新 1 行舊記錄。
  • 0:唯一鍵衝突但 UPDATE 子句未改變任何值(如 SET col=VALUES(col) 且值相同)。

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

多唯一鍵的陷阱

若表有多個唯一索引(如 uk_a(a), uk_b(b)),且插入值同時衝突兩個索引:


INSERT INTO `t` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `c` = `c` + 1;

MySQL 只會更新第一條匹配到的記錄(內部順序不保證),等價於:


UPDATE `t` SET `c` = `c` + 1 WHERE `a`=1 OR `b`=2 LIMIT 1;

最佳實踐: 表僅保留一個業務主鍵/唯一鍵作為 UPSERT 判斷依據,避免多唯一鍵語義模糊。

對比:REPLACE INTO vs ON DUPLICATE KEY UPDATE

特性REPLACE INTOINSERT ... ON DUPLICATE KEY UPDATE
機制DELETE 舊行 + INSERT 新行原地 UPDATE
自增 ID會改變(重新分配)保持不變
外鍵/觸發器觸發 DELETE/INSERT 事件觸發 UPDATE 事件
性能較慢(雙操作+碎片)較快(單操作)
適用場景需重置自增 ID、全列覆蓋增量更新、保持 ID 穩定

常見錯誤與避坑

  1. 忘記建唯一索引: 現象「只插不更新」,檢查 SHOW INDEX FROM t 確認唯一鍵存在。
  2. UPDATE 子句寫死值: ❌ SET c = 'new' → ✅ SET c = VALUES(c)(動態引用輸入值)。
  3. 主鍵為自增但未指定值: INSERT INTO t (b,c) VALUES (1,2) ON DUPLICATE KEY UPDATE ... 若無唯一鍵衝突會產生新自增 ID,非預期「更新」。
  4. 並發高衝突場景: 大量並發 UPSERT 同一鍵會產生行鎖等待,考慮應用層去重併發或分佈式鎖。

總結

INSERT ... ON DUPLICATE KEY UPDATE 是 MySQL 實現高效、原子化 UPSERT 的標準方式。牢記三要素:唯一鍵判斷、VALUES() 引用輸入值、單條 SQL 批量處理。替代「查詢再決定插入/更新」的反模式,讓數據同步、維度表維護、計數器累加等高頻寫入場景既簡潔又高效。

copyright © 萬盛學電腦網 all rights reserved