MySQL 三種插入語句差異全解析：INSERT IGNORE vs INSERT vs REPLACE

MySQL 三種插入語句差異全解析：INSERT IGNORE vs INSERT vs REPLACE

MySQL 提供 INSERT、INSERT IGNORE、REPLACE INTO 三種插入變體，行為差異直接影響資料完整性與業務邏輯。本文通過建表實驗、執行結果對比、底層機制解析，助你精準選型。

快速對比表

實驗驗證腳本

CREATE TABLE testtb (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 1. 初始插入
INSERT INTO testtb(id,name,age) VALUES(1,'bb',13);
SELECT * FROM testtb;  -- 1, 'bb', 13

-- 2. INSERT IGNORE 遇到主鍵衝突
INSERT IGNORE INTO testtb(id,name,age) VALUES(1,'aa',13);
SELECT * FROM testtb;  -- 仍為 1, 'bb', 13 (忽略新值)

-- 3. REPLACE INTO 替換
REPLACE INTO testtb(id,name,age) VALUES(1,'aa',12);
SELECT * FROM testtb;  -- 變為 1, 'aa', 12 (完整替換)

跨表複製場景對比

-- 來源表 table2
id | name
1  | tb
2  | cw
3  | zp

-- 目標表 table1 (id 為 PK)
id | name
1  | tb
2  | zp

-- INSERT IGNORE 複製
INSERT IGNORE INTO table1 SELECT * FROM table2;
-- 結果：table1 變為 1:tb, 2:zp, 3:zp (id=1,2 衝突被忽略，id=3 新增)

-- REPLACE INTO 複製
REPLACE INTO table1 SELECT * FROM table2;
-- 結果：table1 變為 1:tb, 2:cw, 3:zp (id=1,2 刪除重插，id=3 新增)
-- 注意：若 table2 缺少 table1 的某些列 (如 ps)，REPLACE 會將該列置為 NULL/DEFAULT

底層機制深度解析

• INSERT IGNORE： 捕獲 Duplicate Key 錯誤，轉為 Warning，繼續處理後續行。不觸發 衝突行的 BEFORE/AFTER INSERT 觸發器。
• REPLACE INTO： 內部實現為 DELETE FROM table WHERE PK=val; INSERT INTO table ...。
  • 觸發 BEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERT 全套觸發器。
  • 自增 ID 會改變（舊行刪除，新行插入獲取新自增值）。
  • 外鍵關聯：若有子表 ON DELETE CASCADE，會連帶刪除子表資料！
• INSERT ... ON DUPLICATE KEY UPDATE (IDKU)： 更靈活的替代方案，衝突時執行 UPDATE，保留自增 ID、可精確控制更新列。

  INSERT INTO t(id,a,b) VALUES(1,2,3) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b);

選型決策樹

1. 衝突視為錯誤，需業務感知 → INSERT (配合應用層捕獲 1062 錯誤碼)。
2. 衝突視為「已存在即可」，靜默跳過 → INSERT IGNORE (日志導入、幂等寫入)。
3. 衝突需「以新覆蓋舊」，且接受副作用 (觸發器、自增 ID 變化、級聯刪除) → REPLACE INTO。
4. 衝突需「部分更新」，保留自增 ID、不觸發 DELETE 觸發器 → INSERT ... ON DUPLICATE KEY UPDATE (最推薦通用方案)。

效能微基準

• 無衝突時三者效能相近。
• 高衝突率下：INSERT IGNORE 最快 (僅索引查找)；REPLACE 最慢 (DELETE+INSERT 兩次索引維護)；IDKU 介於中間。
• 批次插入建議使用 INSERT ... VALUES (...),(...),... 語法，單條執行開銷巨大。

最佳實踐： 90% 場景首選 INSERT ... ON DUPLICATE KEY UPDATE，語意清晰、副作用可控、效能優異。REPLACE INTO 僅用於「全行覆蓋且接受副作用」的特定場景。INSERT IGNORE 專用於「不關心衝突、追求極致寫入速度」的日志/統計類入庫。