MySQL INSERT 三大實用技巧：INSERT SELECT、INSERT IGNORE、ON DUPLICATE KEY UPDATE

MySQL INSERT 三大實用技巧：INSERT SELECT、INSERT IGNORE、ON DUPLICATE KEY UPDATE

掌握這三種進階插入模式，靈活應對資料遷移、去重、Upsert 等常見業務場景。

技巧一：INSERT ... SELECT —— 從查詢結果插入

場景：表 B 滿足條件的資料複製到表 A。

-- 表 A(id, cola)  表 B(id, cola, colb...)
INSERT INTO A (id, cola)
SELECT id, cola FROM B WHERE colb > 1000;

要求：目標欄位列表與 SELECT 選項數量、順序、型別一一對應。

技巧二：INSERT IGNORE —— 忽略重複鍵錯誤

場景：批量插入時，主鍵/唯一鍵衝突的行靜默跳過，不中斷執行、不報錯。

INSERT IGNORE INTO A (id, cola)
SELECT id, cola FROM B WHERE colb > 1000;

行為：

• 重複主鍵/唯一鍵 → 忽略該行，affected_rows 不計入。
• 其他錯誤（如外鍵失敗、欄位過長）仍會報錯中斷。
• 適用「冪等導入」：同一資料集重複執行安全。

技巧三：INSERT ... ON DUPLICATE KEY UPDATE —— 衝突時更新（Upsert）

場景：表 C 資料同步到表 A，主鍵衝突時更新指定欄位。

INSERT INTO A (id, cola)
SELECT id, cola FROM C
ON DUPLICATE KEY UPDATE A.cola = C.cola;

進階用法：

-- 更新多欄位，使用 VALUES() 參考插入值
INSERT INTO A (id, cola, colb, updated_at)
SELECT id, cola, colb, NOW() FROM C
ON DUPLICATE KEY UPDATE
  cola = VALUES(cola),
  colb = VALUES(colb),
  updated_at = VALUES(updated_at);

-- 計數器累加
INSERT INTO stats (date, cnt) VALUES ('2023-01-01', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

三者對比總結

注意事項

1. ON DUPLICATE KEY UPDATE 觸發條件為主鍵或唯一索引衝突，普通索引不觸發。
2. VALUES(col) 僅在 ON DUPLICATE KEY UPDATE 子句中有效，參考原插入值。
3. INSERT IGNORE 會吞沒所有錯誤（含資料截斷、外鍵失敗），調試困難，生產慎用。
4. 大量資料操作建議分批（每批 1000-5000 行），避免長事務鎖定、Binlog 膨脹。