MySQL 5.7 JSON support

MySQL 5.7 JSON support

Recently, a business needs to store Json and perform some simple business logic processing. I have found that it is difficult to use mysql 5.6 for json data analysis. It is very difficult to simply store data. I have no way to ask.

I first thought of mongodb, and second thought of mysql 5.7. Then, check it out. Oh, it's GA. As we all know, mongodb's engine layer stability has been a short board, while innodb has been very stable after 10 years of verification. So I was wondering if I could try mysql 5.7. I just tried it and found that mysql 5.7 + DRDS does not contain mongoDB at all... Test my simple function: create table json_test (uid int auto_increment, data json, primary key (uid) engine = innodb; create a database 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_te St 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) insert four statements mysql> select data from json_test; + response + | 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"} | + ----------------------------------------------------------- + query the four json statements 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 | + --------- + ------------------- + common group by sum Statistical operations insert into json_test values (NULL, '{"mobile": "15044447277", "amount": 300}'); insert a data without name to check the compatibility of the index with NULL data. 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 create a virtual column and create an index mysql> select user_name, sum (data-> "$. amount ") from json_test where us Er_name = '"name1"'; + ----------- + ----------------------- + | user_name | sum (data-> "$. amount ") | + ----------- + --------------------- + |" name1 "| 700 | + ------------- + --------------------- + view the sum of the data that matches a user_name. Mysql> explain select user_name, sum (data-> "$. amount ") from json_test where user_name = '" name1 "'; + ---- + ------------- + ----------- + ------------ + ------ + --------------- + -------------- + --------- + ------- + ------ + ---------- + ------- + | id | select_type | table | partitions | type | primary | key | key_len | ref | rows | filtered | Extra | + ---- + ------------- + ----------- + ------------ + ------ + ------------ --- + -------------- + --------- + ------- + ------ + ---------- + ------- + | 1 | SIMPLE | json_test | NULL | ref | idx_username | 131 | const | 2 | 100.00 | NULL | + ---- + ------------- + ----------- + ------------ + ------ + --------------- + -------------- + --------- + ------- + ------ + ---------- + ------- + confirm that the data with the index has been taken to the index ======== then, transactions are supported .. This is awesome .. Mysql> start transaction; Query OK, 0 rows affected (0.00 sec) Enable transaction mysql> select * from json_test; + ----- + users + ----------- + | uid | data | user_name | + ----- + users + ----------- + | 1 | {"name": "name1", "amount": 400, "mobile": "15044447279"} | "name1" | 2 | {"name": "name1", "amou Nt ": 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 | + ----- + ------------------------------------------------------------------- + -- --------- + View the original table. Mysql> insert into json_test (uid, data) values (NULL, '{"name": "name1", "mobile": "15044447279", "amount ": 300} '); Query OK, 1 row affected (0.00 sec) Insert new data mysql> select * from json_test; + ----- + certificate + ----------- + | uid | data | user_name | + ----- + --------------------------------------------------------------- + ----------- + | 1 | {"name": "na Me1 "," 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) confirm new data mysql> rollback; roll back data mysql> select * from json_test; + ----- + response + ----------- + | 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) for mysql 5.7 goodwill * 2 .. Slot. Guess the virtual column practice: alter table json_test ADD user_name varchar (128) generated always as (json_extract (data, '$. name ') VIRTUAL; when writing data, make a trigger. Each json operation is json_extract (data,' $. name '), and then write it to a new column that cannot be modified. This allows the original row-store and document to have a perfect combination. When the business changes greatly, it is placed in the json file. When the changes are stable, it is migrated to the row-store. Perfect. Recommended!

[Translation] normalizr)

MySQL5.7 introduction to the JSON type

This article permanently updates the link address: