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。
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 原子執行,無鎖競爭。
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 | INSERT ... ON DUPLICATE KEY UPDATE |
|---|---|---|
| 機制 | DELETE 舊行 + INSERT 新行 | 原地 UPDATE |
| 自增 ID | 會改變(重新分配) | 保持不變 |
| 外鍵/觸發器 | 觸發 DELETE/INSERT 事件 | 觸發 UPDATE 事件 |
| 性能 | 較慢(雙操作+碎片) | 較快(單操作) |
| 適用場景 | 需重置自增 ID、全列覆蓋 | 增量更新、保持 ID 穩定 |
SHOW INDEX FROM t 確認唯一鍵存在。SET c = 'new' → ✅ SET c = VALUES(c)(動態引用輸入值)。INSERT INTO t (b,c) VALUES (1,2) ON DUPLICATE KEY UPDATE ... 若無唯一鍵衝突會產生新自增 ID,非預期「更新」。INSERT ... ON DUPLICATE KEY UPDATE 是 MySQL 實現高效、原子化 UPSERT 的標準方式。牢記三要素:唯一鍵判斷、VALUES() 引用輸入值、單條 SQL 批量處理。替代「查詢再決定插入/更新」的反模式,讓數據同步、維度表維護、計數器累加等高頻寫入場景既簡潔又高效。