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 結束後會自動丟掉暫存表格。

留言

  1. 不錯的方法,感謝分享啦...

    回覆刪除
  2. 如果假設已有一個table的資料,裡面存放的是積分明細,當要更新到總積分的table時,您覺得要再建立TEMP來做更新嗎
    與直接下UPDATE的速度比較哪個比較快呢
    謝謝

    回覆刪除
  3. 不太明白你問題的描述, 需要更明確的描述 (如資料的量、表格 schema、index 更新方式等), 才有比較明確的想法。無論如何, 最穩當的作法是建立一個可重覆實驗的環境, 兩個都作看看, 會比較準。

    回覆刪除

張貼留言

這個網誌中的熱門文章

(C/C++ ) 如何在 Linux 上使用自行編譯的第三方函式庫

熟悉系統工具好處多多

virtualbox 使用 USB 裝置