萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL INSERT IGNORE vs REPLACE INTO 深度對比:避免重複資料的正確姿勢

業務開發中頻繁遇到「寫入不重複」需求:多執行緒併發寫入、定時任務重試、異步消息冪等。MySQL 提供 INSERT IGNORE 和 REPLACE INTO 兩種機制,但兩者行為差異巨大,選錯會導致資料丟失、主鍵跳躍、死鎖風險。本文從底層原理、適用場景、效能影響、最佳實踐四個維度深度對比。

核心機制對比

維度INSERT IGNOREREPLACE INTO
核心邏輯忽略唯一鍵衝突錯誤,**不寫入**新行唯一鍵衝突時,**DELETE 舊行 + INSERT 新行**
資料變更衝突時**零修改**衝突時**刪除舊行、插入新行** (兩次寫入)
主鍵/自增 ID不佔用自增 ID**消耗自增 ID** (DELETE+INSERT 觸發自增)
觸發器觸發不觸發 BEFORE/AFTER INSERT (衝突時)觸發 BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT
外鍵關聯安全 (不刪除父行)**危險**:若被參考,DELETE 會受限或級聯刪除
權限需求INSERTINSERT + DELETE
適用索引PRIMARY KEY / UNIQUE KEYPRIMARY 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;

場景 1:首次插入 (無衝突)

INSERT IGNORE INTO tasks (task_key, status) VALUES ('task_001', 1);
REPLACE INTO tasks (task_key, status) VALUES ('task_001', 1);
-- 兩者效果相同:寫入一行,id=1

場景 2:重複 task_key 再次寫入

-- 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)

場景 3:併發寫入同一 task_key (模擬多執行緒)

-- 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

效能與副作用深度分析

INSERT IGNORE 優勢

  • 零寫入開銷: 衝突時僅檢查唯一索引,無行鎖、無 Redo/Undo Log 產生
  • 自增 ID 連續: 不浪費自增值,避免 BIGINT 耗盡風險 (極端高併發下)
  • 觸發器安全: 不誤觸發審計/同步觸發器
  • 主從複寫安全: Binlog 記錄為 INSERT IGNORE,從庫行為一致

REPLACE INTO 風險

  • 雙倍寫入放大: 衝突時產生 DELETE + INSERT 兩筆 Redo Log、Binlog、Undo Log
  • 自增 ID 跳躍: 高併發下自增值快速增長,長期運行可能耗盡 BIGINT (極端案例)
  • 外鍵級聯刪除災難: 若被子表引用且 ON DELETE CASCADE,會誤刪關聯資料
  • 觸發器副作用: 審計日誌會記錄「刪除+新增」而非「更新」,誤導業務分析
  • 間隙鎖風險: REPLACE 先 DELETE 再 INSERT,在 RR 隔離級別下會持有間隙鎖,增加死鎖概率

選型決策樹:業務場景怎麼選?


需要「寫入不重複」?
    │
    ├─ 需要**更新**現有行的其他欄位 (如 updated_at、status、counter)?
    │       ├─ YES → 推薦:INSERT ... ON DUPLICATE KEY UPDATE (最佳平衡)
    │       └─ NO  → 只需「不重複寫入」,不更新任何欄位?
    │               ├─ YES → INSERT IGNORE (最高效、最安全)
    │               └─ NO  → 極少見
    │
    └─ 歷史包袱:舊代碼已用 REPLACE INTO?
            → 評估風險,漸進式重構為 ON DUPLICATE KEY UPDATE

黃金替代方案:INSERT ... ON DUPLICATE KEY UPDATE (ODKU)

結合兩者優點:有衝突則**更新指定欄位**,無衝突則**插入新行**,單條 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);

ODKU 進階技巧

  • 條件更新: ON DUPLICATE KEY UPDATE status = IF(VALUES(status) > status, VALUES(status), status) (只增不減)
  • 計數器原子遞增: ON DUPLICATE KEY UPDATE cnt = cnt + 1
  • 部分欄位更新: 僅列出需更新欄位,其它保持原值

生產環境最佳實踐清單

  1. 首選 ODKU: 90% 「寫入或更新」場景適用,語意明確、效能最優
  2. 次選 INSERT IGNORE: 純幂等寫入 (日誌埋點、事件去重、冪等消費)
  3. 禁用 REPLACE INTO: 除非極簡單無外鍵、無觸發器、無自增 ID 依賴的查找表維護
  4. 唯一索引設計: 業務唯一鍵 (如 task_key) 必須建立 UNIQUE KEY,勿依賴主鍵
  5. 監控指標: 監控 Innodb_rows_deleted 飆升 → 可能濫用 REPLACE;監控 Questions / Com_insert 比例 → 發現 IGNORE 過多

總結

INSERT IGNORE = 「不存在才插入,存在就算了」 (冪等寫入)
REPLACE INTO = 「刪除舊的,插入新的」 (破壞性替換,副作用多)
INSERT ... ON DUPLICATE KEY UPDATE = 「不存在插入,存在更新」 (**生產首選**)

理解底層機制,拒絕盲目複製貼上,讓資料庫操作既正確又高效。

copyright © 萬盛學電腦網 all rights reserved