業務開發中頻繁遇到「寫入不重複」需求:多執行緒併發寫入、定時任務重試、異步消息冪等。MySQL 提供 INSERT IGNORE 和 REPLACE INTO 兩種機制,但兩者行為差異巨大,選錯會導致資料丟失、主鍵跳躍、死鎖風險。本文從底層原理、適用場景、效能影響、最佳實踐四個維度深度對比。
| 維度 | INSERT IGNORE | REPLACE INTO |
|---|---|---|
| 核心邏輯 | 忽略唯一鍵衝突錯誤,**不寫入**新行 | 唯一鍵衝突時,**DELETE 舊行 + INSERT 新行** |
| 資料變更 | 衝突時**零修改** | 衝突時**刪除舊行、插入新行** (兩次寫入) |
| 主鍵/自增 ID | 不佔用自增 ID | **消耗自增 ID** (DELETE+INSERT 觸發自增) |
| 觸發器觸發 | 不觸發 BEFORE/AFTER INSERT (衝突時) | 觸發 BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT |
| 外鍵關聯 | 安全 (不刪除父行) | **危險**:若被參考,DELETE 會受限或級聯刪除 |
| 權限需求 | INSERT | INSERT + DELETE |
| 適用索引 | PRIMARY KEY / UNIQUE KEY | PRIMARY KEY / UNIQUE KEY |
CREATE TABLE tasks (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
task_key VARCHAR(64) NOT NULL,
status TINYINT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_task_key (task_key)
) ENGINE=InnoDB;
INSERT IGNORE INTO tasks (task_key, status) VALUES ('task_001', 1);
REPLACE INTO tasks (task_key, status) VALUES ('task_001', 1);
-- 兩者效果相同:寫入一行,id=1
-- INSERT IGNORE:靜默忽略,表內資料不變,id 仍為 1
INSERT IGNORE INTO tasks (task_key, status) VALUES ('task_001', 2);
-- Query OK, 0 rows affected, 1 warning (Duplicate entry)
-- REPLACE INTO:刪除 id=1,插入新行 id=2,status 更新為 2
REPLACE INTO tasks (task_key, status) VALUES ('task_001', 2);
-- Query OK, 2 rows affected (1 deleted + 1 inserted)
-- Session A
INSERT IGNORE INTO tasks (task_key, status) VALUES ('task_002', 1);
-- Session B (幾乎同時)
INSERT IGNORE INTO tasks (task_key, status) VALUES ('task_002', 2);
-- 結果:寫入成功 1 行,status=1 (先到者勝),第 2 行被忽略,無鎖等待
-- 換成 REPLACE INTO
-- Session A: REPLACE ... ('task_003', 1)
-- Session B: REPLACE ... ('task_003', 2)
-- 結果:可能發生死鎖 (DELETE+INSERT 鎖升級),或最終 status=2 (後到者勝),但消耗 2 個自增 ID
BIGINT 耗盡風險 (極端高併發下)INSERT IGNORE,從庫行為一致ON DELETE CASCADE,會誤刪關聯資料
需要「寫入不重複」?
│
├─ 需要**更新**現有行的其他欄位 (如 updated_at、status、counter)?
│ ├─ YES → 推薦:INSERT ... ON DUPLICATE KEY UPDATE (最佳平衡)
│ └─ NO → 只需「不重複寫入」,不更新任何欄位?
│ ├─ YES → INSERT IGNORE (最高效、最安全)
│ └─ NO → 極少見
│
└─ 歷史包袱:舊代碼已用 REPLACE INTO?
→ 評估風險,漸進式重構為 ON DUPLICATE KEY UPDATE
結合兩者優點:有衝突則**更新指定欄位**,無衝突則**插入新行**,單條 SQL、原子操作、不消耗額外自增 ID、不觸發 DELETE 觸發器。
INSERT INTO tasks (task_key, status, updated_at)
VALUES ('task_001', 2, NOW())
ON DUPLICATE KEY UPDATE
status = VALUES(status),
updated_at = VALUES(updated_at);
ON DUPLICATE KEY UPDATE status = IF(VALUES(status) > status, VALUES(status), status) (只增不減)ON DUPLICATE KEY UPDATE cnt = cnt + 1task_key) 必須建立 UNIQUE KEY,勿依賴主鍵Innodb_rows_deleted 飆升 → 可能濫用 REPLACE;監控 Questions / Com_insert 比例 → 發現 IGNORE 過多INSERT IGNORE = 「不存在才插入,存在就算了」 (冪等寫入)
REPLACE INTO = 「刪除舊的,插入新的」 (破壞性替換,副作用多)
INSERT ... ON DUPLICATE KEY UPDATE = 「不存在插入,存在更新」 (**生產首選**)
理解底層機制,拒絕盲目複製貼上,讓資料庫操作既正確又高效。