mysql 快速批次更新資料的方法

一直忘了記, 來簡記一下。

若要更新表格 target 裡部份資料欄位 name 的值的話, 假設 id 是 target 的 primary key, 做法如下:

1. CREATE TEMPORARY TABLE temp ... ENGINE = MEMORY;
2. INERT INTO temp ... VALUES ..., ...;
3. UPDATE target, temp SET target.name = temp.name WHERE target.id = temp.id;

先開一個暫存用的 table, 記得 storage engine 選 MEMORY, 存在記憶體待會兒塞入資料較快。欄位只要有和 target 一模一樣的 value 和 id 即可。前者存更新的值, 後者是待會做 join update 時用的。接著將要更新的值塞入 temp, 記得用批次塞入的方法。最後就是將兩個 table join 起來再更新對應的資料。

這個作法的好處是, 當需要更新十萬筆資料時, 只要三個 SQL 就搞定。反之, 若下十萬次 SQL 更新, 會因需要連線多次而慢很多。用一個 SQL 更新全部資料, 也可能減少存取硬碟的次數。

用 TEMPORARY 建立暫存表格的好處是, 這個表格只有這個 connection 看得到, 也不用擔心衝到名稱 (若原本有 temp, temp 會被暫時隱藏起來)。待 connection 結束後會自動丟掉暫存表格。