最近有個業(yè)務(wù)需要能夠存儲Json并做一些簡單的業(yè)務(wù)邏輯處理。業(yè)務(wù)找到我說json的數(shù)據(jù)分析很難用mysql 5.6,這樣的純粹行存來處理難度很大嚣州，問我有沒啥辦法。
我第一想到的是mongodb，第二想到的就是mysql 5.7 溢陪。 然后一查，哎呀睛廊，已經(jīng)GA了形真。眾所周知的，mongodb的引擎層的穩(wěn)定性一直是短板超全，而innodb經(jīng)過10年的驗證咆霜，已經(jīng)是非常穩(wěn)定的東西了邓馒。
所以就在想，是不是可以試試mysql 5.7 蛾坯， 簡單嘗試光酣，發(fā)現(xiàn)mysql 5.7+?DRDS?，完全不帶mongoDB玩啊脉课。救军。。
看看我的簡單功能測試：
create table json_test ( uid int auto_increment,datajson,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 |
+---------+-----------------------+
>
insert into json_test values (NULL,'{"mobile":"15044447277","amount":300}');
>
插入一個不帶name的數(shù)據(jù)下翎，看看索引對空數(shù)據(jù)的兼容性情況缤言。
mysql> ALTER TABLE json_test ?ADDuser_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
>
創(chuàng)建虛擬列并建立索引
mysql> select user_name,sum(data->"$.amount") fromjson_test where user_name = '"name1"';
+-----------+-----------------------+
| user_name | sum(data->"$.amount") |
+-----------+-----------------------+
| "name1" ? | ?? ? ?? ? ?? ? 700 |
+-----------+-----------------------+
>
查看符合某個user_name的數(shù)據(jù)的sum。
mysql> explain select user_name,sum(data->"$.amount")from json_test where user_name = '"name1"';
>
然后视事，這東西竟然還支持事務(wù)胆萧。。這個就牛逼大了俐东。跌穗。
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? ? ?|
+-----+-------------------------------------------------------------------+-----------+
>
Query OK, 1 row affected (0.00 sec)
>
| 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" ?|
+-----+-------------------------------------------------------------------+-----------+
>
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? ? ?|
+-----+-------------------------------------------------------------------+-----------+
>
ALTER TABLE json_test ?ADD user_namevarchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name'))VIRTUAL;
>
應該寫入的時候做一個trigger 每個json都運算json_extract(data,'$.name')砌庄，然后寫到一個新的不可修改的列里羹唠。
這個就可以讓原來的行存和文檔有一個非常完美的結(jié)合，當業(yè)務(wù)變化大的時候娄昆，放到j(luò)son里面佩微，而當變化穩(wěn)定下來，就遷移到行存里萌焰。
完美哺眯。 推薦！
微博_ID:淘寶沈詢_WhisperXD
http://weibo.com/p/230418693f08470102wi35
