[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;