MySQL 批量更新高性能方案：CASE WHEN、INSERT ON DUPLICATE KEY、臨時表三大實戰模式

場景痛點

業務需一次性更新成百上千條記錄，每條記錄更新值不同。循環執行單條 UPDATE 會導致：網絡往返開銷大、事務頻繁提交、鎖競爭激烈、執行時間呈線性增長。

方案一：CASE WHEN 單語句批量更新 (中小數據量首選)

原理： 利用 CASE WHEN 在單條 SQL 中構建「ID -> 新值」映射，配合 WHERE id IN (...) 限制掃描範圍。

UPDATE categories
SET display_order = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
    END,
    title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3);

PHP 構建示例：

$data = [1=>3, 2=>4, 3=>5];  // id => new_value
$ids = implode(',', array_keys($data));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($data as $id => $val) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $val);
}
$sql .= "END WHERE id IN ($ids)";
// 執行 $sql

優點： 單次交互、原子性、語法簡單。
缺點： SQL 長度受 max_allowed_packet 限制 (默認 4MB/64MB)，超大批次需分片；更新列多時 SQL 膨脹明顯。

方案二：INSERT ... ON DUPLICATE KEY UPDATE (大批量、高併發首選)

前提： 表有 主鍵或唯一鍵。
原理： 嘗試插入，主鍵衝突則轉為更新，MySQL 內部優化為單次掃描、單次加鎖。

INSERT INTO test_tbl (id, dr) VALUES (1,'2'), (2,'3'), ..., (10000,'x')
ON DUPLICATE KEY UPDATE dr = VALUES(dr);

對比 REPLACE INTO：

結論： 絕大多數場景首選 ON DUPLICATE KEY UPDATE，數據完整性更強。

方案三：臨時表載入 + 多表 UPDATE (超大批量、跨庫同步)

-- 1. 創建臨時表 (需 CREATE TEMPORARY TABLES 權限)
CREATE TEMPORARY TABLE tmp_upd (id INT PRIMARY KEY, dr VARCHAR(50));

-- 2. 批量導入數據 (LOAD DATA INFILE 極快)
LOAD DATA LOCAL INFILE '/path/data.csv' INTO TABLE tmp_upd FIELDS TERMINATED BY ',';
-- 或 INSERT INTO tmp_upd VALUES ... (分批提交)

-- 3. 多表關聯更新
UPDATE test_tbl t
JOIN tmp_upd tmp ON t.id = tmp.id
SET t.dr = tmp.dr;

-- 4. 會話結束自動清理臨時表

優勢： 解耦數據導入與更新邏輯，LOAD DATA 導入速度遠超 INSERT，適合 10 萬+ 行級批量更新。

性能實測對比 (10 萬條數據，MySQL 5.7, SSD)

最佳實踐建議

1. ≤ 1000 條：CASE WHEN 單語句，代碼簡潔。
2. 1000 ~ 50000 條：INSERT ... ON DUPLICATE KEY UPDATE，分批 1000 行提交，事務包裹。
3. > 50000 條：LOAD DATA -> 臨時表 -> JOIN UPDATE，極致吞吐。
4. 所有方案務必在 WHERE/JOIN 條件上命中主鍵/唯一索引，避免全表鎖。
5. 業務層做冪等性設計，重試不產生髒數據。