「存在即更新,不存在即插入」(Upsert)是高併發寫入場景的核心需求。MySQL 提供 REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE 兩大原生方案,本文深度剖析機制差異、效能特徵與選型陷阱。
兩種語法必須依賴表上的 主鍵 或 唯一索引 判斷衝突。無唯一約束 = 無衝突檢測 = 純插入行為。
REPLACE [LOW_PRIORITY | DELAYED] INTO tbl_name [(col_list)] VALUES (val_list);
-- 或
REPLACE INTO tbl_name SET col1=val1, col2=val2...;
-- 或
REPLACE INTO tbl_name SELECT ...;
內部執行邏輯:
CREATE TABLE `t_test` (
`ikey` INT(11) NOT NULL DEFAULT 0,
`value` VARCHAR(21) NOT NULL DEFAULT '',
`icount` INT(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ikey`),
UNIQUE KEY `uk_value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
-- 初始資料
INSERT INTO `t_test` VALUES (1, 'a', 10), (2, 'b', 20);
-- REPLACE 衝突主鍵
REPLACE INTO `t_test` SET `ikey`=1, `value`='c', `icount`=0;
-- 結果:刪除 (1,'a',10) → 插入 (1,'c',0) → 影響行數 2
-- REPLACE 衝突唯一鍵
REPLACE INTO `t_test` SET `ikey`=3, `value`='a', `icount`=0;
-- 結果:刪除 (1,'c',0) → 插入 (3,'a',0) → 影響行數 2
-- REPLACE 同時衝突主鍵+唯一鍵(不同行)
REPLACE INTO `t_test` SET `ikey`=1, `value`='b', `icount`=0;
-- 結果:刪除 (1,'c',0) 和 (2,'b',20) → 插入 (1,'b',0) → 影響行數 3 ⚠️
INSERT INTO tbl_name (col_list) VALUES (val_list)
ON DUPLICATE KEY UPDATE col1=val1, col2=val2...;
內部執行邏輯:
ON DUPLICATE KEY UPDATE 子句未實際修改值(如 SET col=col)-- 同樣初始資料
INSERT INTO `t_test` SET `ikey`=1, `value`='c', `icount`=0
ON DUPLICATE KEY UPDATE `icount`=`icount`+1;
-- 結果:更新 (1,'a',10) → (1,'a',11) → 影響行數 2
-- 基於原值運算(計數器模式)
INSERT INTO `t_test` SET `ikey`=1, `value`='a', `icount`=1
ON DUPLICATE KEY UPDATE `icount`=`icount`+1;
-- 衝突時 icount 遞增,不衝突則插入 icount=1
| 維度 | REPLACE INTO | INSERT ... ON DUPLICATE KEY UPDATE |
|---|---|---|
| 核心機制 | DELETE + INSERT | UPDATE (原地修改) |
| 自增主鍵行為 | 重新生成新 ID | 保留原 ID |
| 未指定欄位值 | 重置為預設值/NULL | 保留原值 |
| 多唯一索引衝突 | 刪除所有衝突行 | 僅更新第一個匹配行 |
| 觸發器觸發 | BEFORE/AFTER DELETE + INSERT | BEFORE/AFTER UPDATE |
| 外鍵約束 | 可能違反 FK (刪除父行) | 安全(不刪除行) |
| 權限需求 | INSERT + DELETE | INSERT + UPDATE |
| Binlog 記錄 | 記錄為 DELETE + INSERT | 記錄為 UPDATE |
| 適用場景 | 全行替換、結構同步、無外鍵 | 計數器、部分欄位更新、有外鍵 |
INSERT INTO `stats` (`date`, `type`, `count`)
VALUES ('2023-10-15', 'login', 1)
ON DUPLICATE KEY UPDATE
`count` = `count` + VALUES(`count`); -- 引用插入值中的 1
-- 批量 Upsert
INSERT INTO `stats` (`date`, `type`, `count`) VALUES
('2023-10-15', 'login', 10),
('2023-10-15', 'purchase', 5)
ON DUPLICATE KEY UPDATE
`count` = `count` + VALUES(`count`);
VALUES 列表控制在 1000-5000 行,避免封包過大INSERT IGNORE + 應用層重試,或分佈式鎖保護熱點行Innodb_row_lock_waits、Innodb_deadlocks 指標Upsert 不是銀彈。理解 REPLACE 的「毀滅性替換」與 ON DUPLICATE KEY UPDATE 的「精準修補」本質差異,結合業務語義與資料模型約束選型,才能在高併發寫入中兼顧正確性與效能。