MySQL INSERT ... ON DUPLICATE KEY UPDATE 實現批量 upsert

標準 UPDATE 不支援批量多條件更新，逐條執行效能極差。MySQL 專有語法 INSERT ... ON DUPLICATE KEY UPDATE 可在「主鍵/唯一鍵衝突時轉為更新」，實現單語句批量 upsert。

語法示例

INSERT INTO t_member (id, name, email) VALUES
  (1, 'nick',  'nick@126.com'),
  (4, 'angel', 'angel@163.com'),
  (7, 'brank', 'ba198@126.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

核心要點

• id 必須為主鍵或唯一索引，否則不會觸發 DUPLICATE KEY
• VALUES(col) 引用 INSERT 列表中對應列的值，避免重複寫死
• 非衝突行正常插入，衝突行更新指定列，其它列保持原值
• 非標準 SQL，僅 MySQL 支援；PostgreSQL 用 ON CONFLICT，SQL Server 用 MERGE

適用場景

• 批量同步外部數據 (如 API 拉取、ETL 落地)
• 計數器增量更新：ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt)
• 避免「先查後插/更新」的競態條件與雙倍圍數

效能提示

• 單次 VALUES 建議 500-1000 行，超大批次拆分事務
• 配合 LOAD DATA INFILE + 臨時表 再 upsert，導入百萬級更快