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: 0

mysql> 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里面，而當變化穩定下來，就遷移到行存里。

完美。 推薦！

來自： http://blog.sina.com.cn/s/blog_693f08470102wi35.html