MySQL 批量更新同一字段不同值：CASE WHEN、REPLACE INTO、INSERT ON DUPLICATE KEY、臨時表四大方案對比

場景

需一次性將多行記錄的 myfield 更新為不同值，避免循環 UPDATE 產生的高延遲、鎖競爭、網路往返。

方案一：CASE WHEN 單語句批量更新 (通用、無結構風險)

UPDATE mytable
SET myfield = CASE id
    WHEN 1 THEN 'v1'
    WHEN 2 THEN 'v2'
    WHEN 3 THEN 'v3'
END
WHERE id IN (1,2,3);

• PHP 構建範例：

  $ids_values = [1=>11, 2=>22, 3=>33];
  $ids = implode(',', array_keys($ids_values));
  $sql = "UPDATE mytable SET myfield = CASE id ";
  foreach ($ids_values as $id=>$val) $sql .= "WHEN $id THEN $val ";
  $sql .= "END WHERE id IN ($ids)";

• 多欄位同時更新：每欄位一組 CASE WHEN。
• 缺點：SQL 長度受限 (max_allowed_packet)、極大批次需分片。

方案二：REPLACE INTO (主鍵/唯一鍵衝突則刪除重插)

REPLACE INTO mytable (id, myfield) VALUES (1,'v1'),(2,'v2'),(3,'v3');

• 本質：DELETE + INSERT，非衝突列會重置為預設值，慎用於寬表。
• 自增 ID 不變，但可能觸發外鍵級聯刪除。

方案三：INSERT ... ON DUPLICATE KEY UPDATE (僅更新衝突行)

INSERT INTO mytable (id, myfield1, myfield2) VALUES
(1,'v11','v21'),(2,'v12','v22'),(3,'v13','v23')
ON DUPLICATE KEY UPDATE
myfield1=VALUES(myfield1), myfield2=VALUES(myfield2);

• 保留非衝突列原值，適合「upsert」語義。
• VALUES(col) 引用插入值，可進行運算如 myfield2=VALUES(myfield2)+VALUES(id)。

方案四：臨時表 + 多表 UPDATE (大批次首選)

DROP TEMPORARY TABLE IF EXISTS tmptable;
CREATE TEMPORARY TABLE tmptable (id INT PRIMARY KEY, myfield VARCHAR(50));
INSERT INTO tmptable VALUES (1,'v1'),(2,'v2'),(3,'v3');
UPDATE mytable m JOIN tmptable t ON m.id = t.id SET m.myfield = t.myfield;

• 需 CREATE TEMPORARY TABLES 權限。
• 大數據量 (萬行+) 效能最優，避免超長 SQL 解析開銷。
• 可配合 LOAD DATA INFILE 秒級導入臨時表。

效能與選型決策樹

避坑清單

1. 所有方案依賴主鍵或唯一索引，否則無法精準匹配行。
2. REPLACE INTO 會導致 auto_increment 增長、觸發器雙火、外鍵級聯，嚴禁用於有關聯表的主表。
3. CASE WHEN 若 WHERE 範圍超過 WHEN 覆蓋，未匹配行該欄位將置 NULL (非預期) —— 建議 ELSE myfield 保持原值。
4. 大批次建議分塊 (如每 5,000 行) 提交，避免長事務鎖升級、Undo Log 膨脹、主從延遲。

選對工具，批量更新從「整晚跑」變成「秒級完」。