MySQL 批次插入遇上唯一索引衝突：IGNORE / REPLACE / ON DUPLICATE KEY UPDATE 三大策略深度對比

唯一鍵衝突下的批次寫入三板斧：IGNORE vs REPLACE vs ON DUPLICATE KEY UPDATE

批次 INSERT ... SELECT 或應用層批次 INSERT 遇到唯一索引 (主鍵/唯一鍵) 衝突，預設報 1062 錯誤並**整批回滾**。MySQL 提供三種語句級處理策略，行為差異巨大，選型需匹配業務語義。

測試環境建立

CREATE TABLE testtable1 (
  Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  UserId INT UNIQUE KEY,          -- 唯一業務鍵
  UserName VARCHAR(10),
  UserType INT
) ENGINE=InnoDB;

CREATE TABLE testtable2 LIKE testtable1;

-- testtable1 既有資料
INSERT INTO testtable1 (UserId, UserName, UserType) VALUES
(101, 'aa', 1), (102, 'bbb', 2), (103, 'ccc', 3);

-- testtable2 待導入資料 (含衝突 UserId=101)
INSERT INTO testtable2 (UserId, UserName, UserType) VALUES
(201, 'aaa', 1), (202, 'bbb', 2), (203, 'ccc', 3), (101, 'xxxx', 5);

策略一：INSERT IGNORE — 保留舊值，忽略新值

INSERT IGNORE INTO testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2;

• 行為：逐行檢查唯一鍵衝突，衝突行**靜默丟棄**，不衝突行正常插入。
• 結果：UserId=101 保留舊值 ('aa', 1)；新增 201/202/203。
• 副作用：自增 ID 會因衝突行預分配而產生**間隙** (1062 錯誤仍消耗自增值)。
• 適用：冪等導入、日誌去重、不關心衝突行內容。

策略二：REPLACE INTO — 刪除舊行，插入新行 (全欄覆蓋)

REPLACE INTO testtable1 (UserId, UserName)  -- 注意：未指定 UserType
SELECT UserId, UserName FROM testtable2;

• 行為：衝突時**先 DELETE 舊行，再 INSERT 新行** (隱含事務)。
• 關鍵陷阱：未在列表中的欄位會被重置為預設值/NULL，而非保留舊值。
  例中 UserType 變為 NULL (或 0，視定義)。
• 結果：UserId=101 更新為 ('xxxx', NULL)；其餘新增。
• 適用：全欄覆蓋同步、資料以新源為準、表結構簡單 (少欄位)。

策略三：INSERT ... ON DUPLICATE KEY UPDATE — 精準更新指定欄位，保留其餘

-- 僅更新 UserName，保留 UserType
INSERT INTO testtable1 (UserId, UserName, UserType)
SELECT UserId, UserName, UserType FROM testtable2
ON DUPLICATE KEY UPDATE
  UserName = VALUES(UserName);
  -- UserType 不寫 → 保留舊值

• 行為：衝突時**就地更新**指定欄位，不刪除行，自增 ID 不變，其他欄位保持原值。
• VALUES(col) 函數引用插入值，等同 testtable2.UserName。
• 結果：UserId=101 更新 UserName='xxxx'，UserType 保留 1；其餘新增。
• 適用：最通用、最安全：增量同步、計數器累加、部分欄位刷新、审計欄位 (updated_at) 自動維護。

三策略核心差異總結表

進階技巧

• 計數器原子累加：INSERT INTO stat (day, cnt) VALUES ('2024-01-15', 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;
• 多唯一鍵表：ON DUPLICATE KEY UPDATE 觸發任一唯一鍵衝突即更新，需確保業務邏輯無歧義。
• LAST_INSERT_ID(expr)：INSERT ... ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), ... 使衝突時也能獲取該行 ID。
• 批次效能：單條 SQL 多值 INSERT ... VALUES (),(),() ON DUPLICATE KEY UPDATE ... 比循環執行快數量級。

1. 上一篇文章： MySQL 主從不同步兩大解決方案：SQL_SLAVE_SKIP_COUNTER 跳過錯誤 vs 重做從庫完全同步
2. 下一篇文章： MySQL 慢查詢發現與優化實戰：開啟慢查詢日誌、EXPLAIN 分析、索引優化、INFORMATION_SCHEMA 掃描低選擇性索引

MySQL 資料庫相關知識

MySQL 入門完全指南：從關係型概念到 Windows 免安裝配置實戰

一、核心概念：什麼是關係型數據庫？ MySQL 屬於關係型數

phpMyAdmin 無法匯入大檔案解決方案：分離結構與資料 + 專用腳本

核心策略 將匯出/匯入拆分為結構與資料兩階段。結構檔小可直接

Eclipse 連接 MySQL 實戰教學 (重構版)：JDBC 配置、CRUD 驗證與批次插入測試

Eclipse 整合 MySQL 完整實戰流程：環境準備、建

深度解析 MySQL 字符集設定：從基本概念到轉換流程、常見亂碼場景與最佳實踐

基本概念 字符：人類語言最小表義符號 (如 A、中)。 編