MySQL 數據寫入四大語句深度對比：INSERT vs REPLACE vs IGNORE vs ON DUPLICATE KEY UPDATE
面對「不存在則插入，存在則更新/忽略」的業務場景，MySQL 提供四種語句。本文從語法、行為、效能、適用場景四維度深度對比，助你精準選型。
一、語法與行為對比表
| 語句 | 語法 | 衝突行為 | 返回影響行數 | 事務安全 | 觸發器觸發 |
|---|
| INSERT | INSERT INTO t (c) VALUES (v) | 報錯 (Duplicate entry) | 1 (成功) / 0 (失敗) | ✅ | BEFORE/AFTER INSERT |
| INSERT IGNORE | INSERT IGNORE INTO t (c) VALUES (v) | 忽略錯誤，不插入，不報錯 | 1 (插入) / 0 (忽略) | ✅ | 同上 |
| REPLACE | REPLACE INTO t (c) VALUES (v) | 刪除舊行 + 插入新行 | 1 (純插入) / 2 (替換) / >2 (多唯一鍵) | ✅ | DELETE + INSERT 觸發器 |
| ON DUPLICATE KEY UPDATE | INSERT INTO t (c) VALUES (v) ON DUPLICATE KEY UPDATE c=VALUES(c) | 更新指定列 | 1 (插入) / 2 (更新) | ✅ | BEFORE/AFTER UPDATE |
二、核心差異深度解析
1. INSERT — 基礎插入
- 要求：目標表有主鍵或唯一索引才能觸發衝突檢測。
- 多行插入：
INSERT INTO t (c) VALUES (1), (2), (3); 單條 SQL，效能遠超循環插入。
INSERT INTO t () VALUES ()：全列使用默認值。
2. INSERT IGNORE — 靜默忽略
- 適用：日誌寫入、冪等導入、不關心衝突詳情。
- 風險：吞噬所有錯誤（不僅重複鍵，還包括數據截斷、外鍵失敗等），排查困難。
- 不觸發
ON DUPLICATE KEY UPDATE 邏輯。
3. REPLACE — 刪除重插
- 本質：
DELETE + INSERT 原子操作。
- 副作用：
- 自增 ID 會改變（舊行刪除，新行新 ID）。
- 觸發
BEFORE/AFTER DELETE 和 BEFORE/AFTER INSERT 觸發器。
- 若表有多個唯一索引，任一衝突均觸發全行刪除重插，返回行數 >2。
- 適用：數據全量覆蓋、不在意自增 ID 變化、無外鍵引用。
4. INSERT ... ON DUPLICATE KEY UPDATE — 精準更新 (推薦)
- 僅更新指定列，保留自增 ID、其他列不變。
VALUES(col) 引用插入值，避免重複寫字面量。- 可混合：
INSERT ... ON DUPLICATE KEY UPDATE a=VALUES(a), b=b+1 (b 遞增)。
- 觸發
BEFORE/AFTER UPDATE 觸發器。
- 最佳實踐：高併發寫入、計數器更新、用戶資料同步。
三、效能對比 (InnoDB, 唯一索引衝突場景)
| 語句 | 鎖模式 | 索引維護 | 紅黑樹操作 | 推薦指數 |
|---|
| INSERT | 插入意向鎖 + 記錄鎖 | 1 次 | 1 次插入 | ⭐⭐⭐ (無衝突) |
| INSERT IGNORE | 同 INSERT，衝突時釋放 | 1 次 (成功) / 0 (忽略) | 同 INSERT | ⭐⭐ (靜默風險) |
| REPLACE | X 鎖 (刪除) + 插入意向鎖 | 2 次 (刪除+插入) | 1 次刪除 + 1 次插入 | ⭐ (副作用大) |
| ON DUPLICATE KEY UPDATE | X 鎖 (更新) | 1 次 (更新) | 1 次更新 | ⭐⭐⭐⭐⭐ (最優) |
四、實戰選型決策樹
開始
├─ 不允許重複，衝突即報錯 → INSERT
├─ 衝突靜默忽略，不關心細節 → INSERT IGNORE (謹慎使用)
├─ 需全行覆蓋，不在意自增 ID 變化 → REPLACE
└─ 需保留自增 ID，精準更新部分列 → INSERT ... ON DUPLICATE KEY UPDATE ⭐ 首選
五、批量寫入最佳實踐
-- 批量 upsert (MySQL 8.0.19+ 支援 VALUES ROW)
INSERT INTO users (id, name, login_count)
VALUES (1, 'Alice', 1), (2, 'Bob', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;
-- 避免大事務：分批 500-1000 行提交
