「存在則更新，不存在則插入」（Upsert）是高併發業務系統的核心寫入模式。MySQL 提供兩大原生方案：INSERT ... ON DUPLICATE KEY UPDATE（保留舊行、增量更新）與 REPLACE INTO（刪除舊行、整行替換）。本文深度對比兩者語法、行為差異、觸發器影響、主鍵/唯一索引交互、效能特徵與最佳選型策略。
INSERT INTO `table` (`id`, `name`, `count`, `updated_at`)
VALUES (1, 'Alice', 1, NOW())
ON DUPLICATE KEY UPDATE
`count` = `count` + 1,
`updated_at` = NOW();
REPLACE INTO `table` (`id`, `name`, `count`, `updated_at`)
VALUES (1, 'Alice', 1, NOW());
-- 等價於：DELETE 舊行 + INSERT 新行
| 特性 | IODKU | REPLACE INTO |
|---|---|---|
| 核心動作 | 更新現有行 | 刪除舊行 + 插入新行 |
| 主鍵/唯一鍵衝突判定 | 任一唯一索引衝突觸發 | 任一唯一索引衝突觸發 |
| 未衝突欄位保留 | ✅ 保留舊值（除非顯式更新） | ❌ 全部按新值重設（缺失欄位變 DEFAULT/NULL） |
| AUTO_INCREMENT 影響 | 不消耗新 ID | 刪除重插 → 消耗新 ID（可能導致 ID 跳躍） |
| 觸發器觸發 | BEFORE/AFTER UPDATE | BEFORE/AFTER DELETE + BEFORE/AFTER INSERT |
| 外鍵級聯 | 不觸發級聯刪除 | ⚠️ 觸發 ON DELETE CASCADE 刪除關聯資料！ |
| 返回值 | 1=新增, 2=更新, 0=無變化 | 1=新增/替換 |
| VALUES() 函數支援 | ✅ VALUES(col) 參考插入值 | ❌ 不支援 |
INSERT INTO `users` (`id`, `name`, `email`, `login_count`, `last_login`)
VALUES (1001, 'Bob', 'bob@example.com', 1, NOW())
ON DUPLICATE KEY UPDATE
`login_count` = `login_count` + 1,
`last_login` = VALUES(`last_login`), -- 使用插入時的 NOW()
`email` = VALUES(`email`); -- 同步更新 email（如有變）
INSERT INTO `stats` (`date`, `pv`, `uv`)
VALUES ('2024-01-15', 100, 50)
ON DUPLICATE KEY UPDATE
`pv` = `pv` + VALUES(`pv`),
`uv` = CASE WHEN VALUES(`uv`) > `uv` THEN VALUES(`uv`) ELSE `uv` END; -- 取最大值
表有 PRIMARY KEY (id) + UNIQUE KEY uk_email (email)：
-- 插入 id=1, email='a@b.com'，表中已有 id=2, email='a@b.com'
INSERT INTO `t` (`id`, `email`) VALUES (1, 'a@b.com')
ON DUPLICATE KEY UPDATE `id` = `id`; -- 只更新 id=2 那行（LIMIT 1 隱含）
行為等價於：UPDATE t SET id=id WHERE id=1 OR email='a@b.com' LIMIT 1;
-- 表結構
CREATE TABLE `t_test` (
`ikey` INT PRIMARY KEY,
`value` VARCHAR(20) UNIQUE, -- 第二個唯一索引
`icount` INT
);
-- 現有資料
INSERT INTO `t_test` VALUES (2, 'a', 10), (1, 'b', 40);
-- 執行 REPLACE
REPLACE INTO `t_test` SET `ikey`=1, `value`='a', `icount`=0;
-- 結果：兩行都被刪除！因為 ikey=1 與 value='a' 分別衝突兩行
-- 然後插入新行 (1, 'a', 0)
-- 原有 (2, 'a', 10) 和 (1, 'b', 40) 全丟失！
而 IODKU 僅更新一行：
INSERT INTO `t_test` SET `ikey`=1, `value`='a', `icount`=0
ON DUPLICATE KEY UPDATE `icount` = `icount` + 1;
-- 僅更新 ikey=1 或 value='a' 匹配的第一行
| 指標 | IODKU | REPLACE INTO |
|---|---|---|
| 寫入開銷 | 單次索引查找 + 更新 | 刪除 (含二級索引維護) + 插入 (含所有索引維護) |
| 二級索引維護 | 僅更新變更欄位的索引 | 全量重建所有索引項 |
| 主鍵為自增 ID 時 | ID 穩定 | ID 會變（舊 ID 釋放，新 ID 分配） |
| 鎖持有時間 | 較短 | 較長（刪除+插入兩階段） |
| 適用場景 | 計數器、增量更新、保留歷史欄位 | 全行替換、無外鍵、ID 無意義 |
需要 Upsert？
├─ 有外鍵級聯 / 多唯一索引 / 需保留未變欄位 / 計數器增量 / ID 需穩定
│ └─ ✅ 必選：INSERT ... ON DUPLICATE KEY UPDATE
├─ 確定無外鍵、單一主鍵、全行替換、ID 無業務意義、簡單場景
│ └─ 可選：REPLACE INTO（但 IODKU 同樣適用且更安全）
└─ 僅需忽略重複（不更新）
└─ INSERT IGNORE INTO ...
$sql = "INSERT INTO `{$ecs->table('cat_lang')}`
(`cat_id`, `lang_id`, `cat_name`, `keywords`, `cat_desc`)
VALUES
({$cat_id}, {$k}, '{$val['cat_name']}', '{$val['keywords']}', '{$val['cat_desc']}')
ON DUPLICATE KEY UPDATE
`cat_name` = VALUES(`cat_name`),
`cat_desc` = VALUES(`cat_desc`),
`keywords` = VALUES(`keywords`)";
-- 表主鍵：PRIMARY KEY (cat_id, lang_id) 雙主鍵
INSERT ... ON DUPLICATE KEY UPDATE 是 95%+ Upsert 場景的正解：語義精確、效能優越、行為可預測、支援增量更新與 VALUES() 參考。REPLACE INTO 因刪除重插語義導致外鍵級聯、多唯一索引全刪、ID 跳躍、觸發器雙觸發等陷阱，僅建議在「無外鍵、單主鍵、全行替換、ID 無意義」的極簡場景使用，且 IODKU 同樣能完美覆蓋這些場景。掌握 IODKU 的 VALUES() 技巧與條件更新寫法，能優雅解決計數器、統計聚合、多欄位同步等高頻業務。
