Google+ Followers

2014年7月17日 星期四

[SQL] 透過 INNER JOIN 更新 Table 新增的欄位數值 @ MySQL 5.6

對於一些當作收集 log 用途的 table tb_log ,隨著時間增加後,通常會再整理另一個 tb_status 的 table,快速查詢各個狀態,設計上就會定期批次從 tb_log 取出資料,存進 tb_status 中。

假設 tb_log 有 5 個欄位,一開始只覺得需要 2 個欄位的資訊,就把 tb_status 設定為 2 個欄位,然而過一陣子後,想多記錄一個欄位時,只好變動 tb_status ,但新增的欄位沒有舊資料,就變成要從 tb_log 取出來再存進 tb_status 了

碰到這種問題,有一個解法就是使用 INNER JOIN 來處理:
  1. 先從 tb_status 找出欄位未有值的資料
  2. 從 tb_log 組出 tb_status 所需的資料
  3. 透過  Update 指令更新
情況敘述:

mysql> describe tb_log;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f0     | int(11)      | NO   | PRI | NULL    | auto_increment |
| f1     | varchar(8)   | YES  |     | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
| f4     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

mysql> describe tb_status;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f1     | varchar(8)   | YES  | PRI | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+


其中 tb_status.f3 則是新建出來,未有資料的。

第一步:先找出 tb_status.f3 是空的(新進資料會有 f3 數值,只有舊資料沒有)

mysql> SELECT f1 WHERE f3 IS NULL;

第二步:從 tb_log 組出 f3 資料,由於 tb_log 是流水帳,且 tb_status 本身也可以從 tb_log 查詢出來的,只需組出 tb_status 需要的欄位即可:

mysql> SELECT f1, f3 FROM tb_log GROUP BY f1;

第三步,把上述兩個資料 JOIN 起來:

SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1, (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2;


最後,追加更新 tb_status 的用法:

UPDATE tb_status AS tb4
INNER JOIN

(
SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1,
(SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2
) AS tb3

ON tb4.f1 = tb3.f1

SET

tb4.f3 = tb3.f3;

沒有留言:

張貼留言