MYSQL:使用臨時表一次性大量UPDATE

by Ben

前言

最近遇到須要更新一堆 DB 欄位的需求
實作後發現不同的寫法對效能的影響滿大的
這就來紀錄分享一下

問題情境

假設我們有2張表
分別是 orderorderDetail
其中 order 是訂單的總表
orderDetail 則是訂單的細節
兩者關係以下圖示之

order
file

orderDetail
file

原始 table

order裡的第一筆 (id=1) 就叫他 apple 訂單吧!
對應到 orderDetail 可以發現 apple 訂單裡面有兩個 item
分別是 雞排 和腿排 (兩者的 fk 為 fk_orderID = 1)

但為了閱讀方便
我們想要在 order 裡多新增一個 items 欄位
把這張訂單裡保括了了哪些品項給列出來 並用逗號隔開

成果如下

修改後 table

file

可以發現每個訂單都有詳細的品項在裡面、並用逗號分開
如 apple 訂單
就有雞排、腿排在列
接下來我們就來看看 SQL 要怎麼寫吧!

SQL 實作

原始 SQL

最起初的想法是直接用 UPDATE 的語法一筆筆資科更新

UPDATE `order` AS o SET o.`items` = (SELECT GROUP_CONCAT(od.`items`) AS 'items' FROM `orderDetail` AS od WHERE od.fk_orderID = o.id GROUP BY o.`id`)

但這樣子的缺點是
對 db 的存取需求量爆大
現在就只有幾筆資料 所以可能還好
但若訂單資料有幾千、幾萬筆的話
這樣子大量的存取就很可能會導致 db timeout
所以可以修改如下

修改後 SQL => 使用臨時表

為了避免上面的問題
所以我們先 SELECT 一個已經把 by id 列好 items 的表出來
並把它存成一個暫存的表叫 name
然後再把 order 表去 join 這張暫存表 name
同時更新 items 欄位

UPDATE `order` AS o INNER JOIN
(SELECT o.`id`, GROUP_CONCAT(od.`items`) AS items
FROM `order_test` AS o
LEFT JOIN `orderDetail_test` od
   ON od.`fk_orderID` = o.`id`
GROUP BY o.`id`) AS name
ON name.`id` = o.`id`
SET o.`items` = name.`items`

這樣就可以達到和前一個 SQL 一樣的成果
但只要存一次暫存表 name 就好
而非跑一堆查詢語法
進而有效改善效能問題

參考資料

https://blog.longwin.com.tw/2013/06/mysql-group-by-group_concat-2013/

You may also like

Leave a Comment