核心語法
INSERT INTO table (cols) VALUES (vals)
ON DUPLICATE KEY UPDATE col1 = expr1, col2 = expr2, ...;
行為邏輯
- 嘗試插入新行
- 若主鍵或唯一鍵衝突 → 更新現有行 指定欄位
- 受影響行數：
1 (新增) / 2 (更新)
實戰範例
1. 計數器原子增長（單欄位更新）
INSERT INTO osc_visit_stats (stat_date, type, id, view_count)
VALUES (?, ?, ?, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;
-- 復合主鍵 (stat_date, type, id)
2. 多欄位更新（訪問記錄：計數+IP+時間）
INSERT INTO osc_space_visit_records (space, user, visit_count, ip, visit_time)
VALUES (?, ?, 1, ?, NOW())
ON DUPLICATE KEY UPDATE
visit_count = visit_count + 1,
ip = VALUES(ip),
visit_time = NOW();
3. VALUES() 函數引用插入值（批量 Upsert 必備）
INSERT INTO table (a, b, c) VALUES (1, 2, 3), (4, 5, 6)
ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);
-- 等效於：
-- (1,2,3) 衝突 → c = 1+2 = 3
-- (4,5,6) 衝突 → c = 4+5 = 9
關鍵注意事項
- 唯一鍵衝突判定：任一唯一索引/主鍵衝突均觸發 UPDATE
- 多唯一鍵風險：表有多個唯一索引時，衝突行不確定（取第一個匹配的），盡量避免 在多唯一鍵表使用
- DELAYED 忽略：
INSERT DELAYED ... ON DUPLICATE KEY UPDATE 中 DELAYED 無效
- 自增 ID：衝突更新不消耗自增值；
REPLACE INTO 會消耗（刪除舊行重新插入）
三大 Upsert 方案對比
| 方案 | 衝突行處理 | 自增 ID | 外鍵安全 | 適用場景 |
|---|
| INSERT IGNORE | 靜默跳過，不插入不更新 | 消耗 | 安全 | 只需「不存在則插入」，如日誌去重 |
| REPLACE INTO | 刪除舊行 + 插入新行 | 消耗（重新分配） | 危險（觸發級聯刪除） | 需完全替換整行、無外鍵依賴 |
| INSERT ... ON DUPLICATE KEY UPDATE | 就地更新指定欄位 | 保留 | 安全 | 計數器、狀態更新、最後訪問時間等首選 |
最佳實踐建議
- 核心業務 Upsert 首選 ON DUPLICATE KEY UPDATE
2. 明確列出更新欄位，避免 UPDATE col = VALUES(col) 全欄位更新
3. 批量 Upsert 使用 VALUES() 引用插入值，單條 SQL 完成多行
4. 表設計階段規劃唯一索引，支撐 Upsert 語義
5. 監控 Innodb_row_lock_waits、死鎖日誌，優化熱點行併發