MySQL 5.7 對JSON的支援

MySQL 5.7 對JSON的支援

最近有個業務需要能夠儲存Json並做一些簡單的商務邏輯處理。業務找到我說json的資料分析很難用mysql 5.6 ,這樣的純粹行存來處理難度很大，問我有沒啥辦法。

我第一想到的是mongodb，第二想到的就是mysql 5.7 。 然後一查，哎呀，已經GA了。眾所周知的，mongodb的引擎層的穩定性一直是短板，而innodb經過10年的驗證，已經是非常穩定的東西了。 所以就在想，是不是可以試試mysql 5.7 ， 簡單嘗試，發現mysql 5.7 + DRDS ，完全不帶mongoDB玩啊。。。 看看我的簡易功能測試： create table json_test ( uid int auto_increment,data json,primary key(uid))engine=innodb; 建庫mysql> insert into json_test values (NULL, '{"name":"name1","mobile":"15044447279","amount":400}');Query OK, 1 row affected (0.01 sec) mysql> insert into json_test values (NULL, '{"name":"name1","mobile":"15044447279","amount":300}');Query OK, 1 row affected (0.01 sec) mysql> insert into json_test values (NULL, '{"name":"name2","mobile":"15044447278","amount":300}');Query OK, 1 row affected (0.01 sec) mysql> insert into json_test values (NULL, '{"name":"name3","mobile":"15044447277","amount":300}');Query OK, 1 row affected (0.01 sec) 插入四條語句mysql> select data from json_test;+-----------------------------------------------------------+| data                                                      |+-----------------------------------------------------------+| {"name": "name1", "amount": 400, "mobile": "15044447279"} || {"name": "name1", "amount": 300, "mobile": "15044447279"} || {"name": "name2", "amount": 300, "mobile": "15044447278"} || {"name": "name3", "amount": 300, "mobile": "15044447277"} |+-----------------------------------------------------------+查詢這四條json語句mysql> select data->"$.name" as name ,sum(data->"$.amount") from json_test group by name;+---------+-----------------------+| name    | sum(data->"$.amount") |+---------+-----------------------+| "name1" |                   700 || "name2" |                   300 || "name3" |                   300 |+---------+-----------------------+做個group by sum 常見的統計操作 insert into json_test values (NULL, '{"mobile":"15044447277","amount":300}');插入一個不帶name的資料，看看索引對空資料的相容性情況。mysql> ALTER TABLE json_test  ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table json_test add index idx_username (user_name);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0建立虛擬列並建立索引mysql> select user_name,sum(data->"$.amount") from json_test where user_name = '"name1"';+-----------+-----------------------+| user_name | sum(data->"$.amount") |+-----------+-----------------------+| "name1"   |                   700 |+-----------+-----------------------+查看符合某個user_name的資料的sum。 mysql> explain select user_name,sum(data->"$.amount") from json_test where user_name = '"name1"';+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | json_test | NULL       | ref  | idx_username  | idx_username | 131     | const |    2 |   100.00 | NULL  |+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ 確認帶索引資料走到了索引上  =========== 然後，這東西竟然還支援事務。。這個就牛逼大了。。 mysql> start transaction;Query OK, 0 rows affected (0.00 sec)開啟事務mysql> select * from json_test;+-----+-------------------------------------------------------------------+-----------+| uid | data                                                              | user_name |+-----+-------------------------------------------------------------------+-----------+|   1 | {"name": "name1", "amount": 400, "mobile": "15044447279"}         | "name1"   ||   2 | {"name": "name1", "amount": 300, "mobile": "15044447279"}         | "name1"   ||   3 | {"name": "name2", "amount": 300, "mobile": "15044447278"}         | "name2"   ||   4 | {"name": "name3", "amount": 300, "mobile": "15044447277"}         | "name3"   ||   5 | {"amount": 300, "mobile": "15044447277"}                          | NULL      ||   6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"}         | NULL      |+-----+-------------------------------------------------------------------+-----------+ 查看原表。 mysql> insert into json_test (uid,data)  values (NULL, '{"name":"name1","mobile":"15044447279","amount":300}');Query OK, 1 row affected (0.00 sec)插入新資料mysql> select * from json_test;                                                                                                                                                                             +-----+-------------------------------------------------------------------+-----------+| uid | data                                                              | user_name |+-----+-------------------------------------------------------------------+-----------+|   1 | {"name": "name1", "amount": 400, "mobile": "15044447279"}         | "name1"   ||   2 | {"name": "name1", "amount": 300, "mobile": "15044447279"}         | "name1"   ||   3 | {"name": "name2", "amount": 300, "mobile": "15044447278"}         | "name2"   ||   4 | {"name": "name3", "amount": 300, "mobile": "15044447277"}         | "name3"   ||   5 | {"amount": 300, "mobile": "15044447277"}                          | NULL      ||   6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"}         | NULL      ||   7 | {"name": "name1", "amount": 300, "mobile": "15044447279"}         | "name1"   |+-----+-------------------------------------------------------------------+-----------+7 rows in set (0.00 sec)確認新資料mysql> rollback;復原資料mysql> select * from json_test;+-----+-------------------------------------------------------------------+-----------+| uid | data                                                              | user_name |+-----+-------------------------------------------------------------------+-----------+|   1 | {"name": "name1", "amount": 400, "mobile": "15044447279"}         | "name1"   ||   2 | {"name": "name1", "amount": 300, "mobile": "15044447279"}         | "name1"   ||   3 | {"name": "name2", "amount": 300, "mobile": "15044447278"}         | "name2"   ||   4 | {"name": "name3", "amount": 300, "mobile": "15044447277"}         | "name3"   ||   5 | {"amount": 300, "mobile": "15044447277"}                          | NULL      ||   6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"}         | NULL      |+-----+-------------------------------------------------------------------+-----------+6 rows in set (0.00 sec)  對mysql 5.7 好感度*2啊。。臥槽。  猜測一下虛擬列的做法： ALTER TABLE json_test  ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL;應該寫入的時候做一個trigger 每個json都運算json_extract(data,'$.name') ，然後寫到一個新的不可修改的列裡。 這個就可以讓原來的行存和文檔有一個非常完美的結合，當業務變化大的時候，放到json裡面，而當變化穩定下來，就遷移到行存裡。 完美。 推薦！

[譯]JSON資料範式化（normalizr） 

MySQL5.7 JSON類型使用介紹 

本文永久更新連結地址：