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

[Linux] 透過 alias 與 virtual 機制建立 no-reply @ hostname 帳號 @ Postfix 2.9.6, Ubuntu 12.04

新增一筆 alias 用來導向 /dev/null

$ grep alias_maps /etc/postfix/main.cf
alias_maps = hash:/etc/aliase
$ sudo vim /etc/aliases
...
devnull: /dev/null
...

採用 virtual account 新增 no-reply 帳號：

$ grep virtual_alias_maps /etc/postfix/main.cf
$ sudo vim /etc/postfix/main.cf
virtual_alias_maps = hash:/etc/postfix/virtual

$ sudo vim /etc/postfix/virtual
no-reply@hostname devnull

$ sudo newaliases
$ sudo postmap /etc/postfix/virtual
$ sudo postfix reload
postfix/postfix-script: refreshing the Postfix mail system

至於測試方式，就直接連他寄信看看：

$ telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 hostname ESMTP Postfix (Ubuntu)
HELO localhost
250 hostname
mail from:<root@localhost>
250 2.1.0 Ok
rcpt to:<no-reply@hostname>
250 2.1.0 Ok
quit
221 2.0.0 Bye
Connection closed by foreign host.

如果出現 451 4.3.0 <no-reply@hostname>: Temporary lookup failure ，請記得翻一下 /var/log/mail.err ，若是單純 error: open database /etc/postfix/virtual.db: No such file or directory 問題，記得跑一下 sudo postmap /etc/postfix/virtual 即可。