Google+ Followers

2017年9月14日 星期四

[MySQL] 從 JSON 抽取資料建立虛擬欄位 @ MySQL 5.7

同事反映 query 很慢,除了改進 SQL 語法外,也小試身手,對 JSON 資料抽出來建立虛擬欄位跟索引,順便筆記一下。

CREATE TABLE `my_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


這邊 data column 是 text 型態,主因是這 DB server 從 5.6 升上來的 XD 且當初讓 data 有很多彈性,不一定是 json 格式。

但,要抽取成虛擬欄位時,建議要保持 data 是 json 格式,就先整理一下資料:

mysql> UPDATE my_data SET data = '{}' WHERE data IS NULL OR data = ''

假想 data 的數值為 {"keyword":"value"},因此抽出 keyword 虛擬欄位來用:

mysql> ALTER TABLE my_data ADD keyword VARCHAR(64) AS (JSON_UNQUOTE(data->>"$. keyword"));
mysql> ALTER TABLE my_data ADD INDEX (keyword);


如此一來,可以改對 keyword 欄位查詢了,可以再加快一點,而 table 狀態更新為:

CREATE TABLE `my_data ` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  `keyword` varchar(64) GENERATED ALWAYS AS (json_unquote(json_unquote(json_extract(`data`,'$. keyword')))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `keyword ` (`keyword `),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

沒有留言:

張貼留言