MySQL 插入數據主鍵衝突三大解決方案：IGNORE、REPLACE、ON DUPLICATE KEY UPDATE

場景

批量導入數據時，部分行主鍵或唯一鍵衝突，需按業務邏輯選擇：跳過、覆蓋、合併更新。

方案對比

實戰示例

建表

CREATE TABLE t (
    id INT PRIMARY KEY,
    val INT UNIQUE
) ENGINE=InnoDB;

1. INSERT IGNORE

INSERT IGNORE INTO t VALUES (1,10), (1,20), (2,30);
-- 結果：只插入 (1,10) 和 (2,30)，(1,20) 靜默丟棄

2. REPLACE INTO

REPLACE INTO t VALUES (1,10), (1,20), (2,30);
-- 過程：插入(1,10) → 衝突刪除(1,10)插入(1,20) → 插入(2,30)
-- 影響行數：4（2次插入+1次刪除+1次插入）

注意：自增主鍵會重新分配，外鍵關聯會斷裂。

3. ON DUPLICATE KEY UPDATE

-- 方式 A：引用表當前值
INSERT INTO t VALUES (1,10) ON DUPLICATE KEY UPDATE val = val + 1;
-- 相當於：INSERT ...; UPDATE t SET val = val + 1 WHERE id = 1;

-- 方式 B：引用插入值（VALUES() 函數）
INSERT INTO t VALUES (1,10) ON DUPLICATE KEY UPDATE val = VALUES(val) + 1;
-- 相當於：UPDATE t SET val = 10 + 1 WHERE id = 1;

複合唯一鍵陷阱

-- 表結構：PRIMARY KEY(id), UNIQUE KEY uk_val(val)
INSERT INTO t VALUES (1,10), (2,10) ON DUPLICATE KEY UPDATE val = VALUES(id) + 1;
-- 唯一鍵 val=10 衝突，但主鍵 id=2 不衝突
-- 執行：UPDATE t SET val = 2+1 WHERE id = 1;  -- 只更新第一行！

核心規則：UPDATE 的 WHERE 條件永遠是發生衝突的那個鍵對應的主鍵值，而非插入行的所有主鍵。

總結選型建議

• 不關心衝突數據 → INSERT IGNORE
• 需全量覆蓋、無外鍵依賴 → REPLACE
• 需增量合併、保持主鍵穩定 → ON DUPLICATE KEY UPDATE（首選）