10分钟了解MySQL5.7对原生JSON的支持与用法

Part1:JSON格式的支持

版本终于支持了原生的JSON格式,即将关系型数据库和文档型NO_SQL数据库集于一身。本文接下来将对这特性分别就和各自实现的方法异同进行介绍和演示。

Part2:创建相应表结构

[root@HE3 ~]# mysql -V

mysql  Ver Distrib , for linux- (x86_64) using  EditLine wrapper

mysql> create database helei;Query OK, 1 row affected ( sec)mysql> use helei;Database changedmysql> create table helei (id int(10) unsigned NOT NULL,context json default null,primary key(id));Query OK, 0 rows affected ( sec)mysql> show create table helei \G*************************** 1. row ***************************       Table: heleiCreate Table: CREATE TABLE `helei` (  `id` int(10) unsigned NOT NULL,  `context` json DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set ( sec)

Part3:构造数据&测试

mysql> desc helei;+---------+------------------+------+-----+---------+-------+| Field   | Type             | Null | Key | Default | Extra |+---------+------------------+------+-----+---------+-------+| id      | int(10) unsigned | NO   | PRI | NULL    |       || context | json             | YES  |     | NULL    |       |+---------+------------------+------+-----+---------+-------+2 rows in set ( sec)mysql> insert into helei values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');Query OK, 3 rows affected ( sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from helei;+----+----------------------------------+| id | context                          |+----+----------------------------------+|  1 | {"age": 100, "name": "贺磊"}     ||  2 | {"age": 30, "name": "陈加持"}    ||  3 | {"age": 28, "name": "于浩"}      |+----+----------------------------------+3 rows in set ( sec)mysql> select id,JSON_EXTRACT(context,'$.name') name,JSON_EXTRACT(context,'$.age') age from helei;+----+-------------+------+| id | name        | age  |+----+-------------+------+|  1 | "贺磊"      | 100  ||  2 | "陈加持"    | 30   ||  3 | "于浩"      | 28   |+----+-------------+------+3 rows in set ( sec)获取Key-Valuemysql> select id,json_keys(context) from helei;+----+--------------------+| id | json_keys(context) |+----+--------------------+|  1 | ["age", "name"]    ||  2 | ["age", "name"]    ||  3 | ["age", "name"]    |+----+--------------------+3 rows in set ( sec)获取全部Keymysql> update helei set context=JSON_INSERT(context,'$.name',"贺磊",'$.address','beijing')where id=1;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from helei;+----+------------------------------------------------------+| id | context                                              |+----+------------------------------------------------------+|  1 | {"age": 100, "name": "贺磊", "address": "beijing"}   ||  2 | {"age": 30, "name": "陈加持"}                        ||  3 | {"age": 28, "name": "于浩"}                          |+----+------------------------------------------------------+3 rows in set ( sec)增加Key-Valuemysql> update helei set context=JSON_SET(context,'$.name',"高穷帅")where id=1;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from helei;+----+---------------------------------------------------------+| id | context                                                 |+----+---------------------------------------------------------+|  1 | {"age": 100, "name": "高穷帅", "address": "beijing"}    ||  2 | {"age": 30, "name": "陈加持"}                           ||  3 | {"age": 28, "name": "于浩"}                             |+----+---------------------------------------------------------+3 rows in set ( sec)变更key-valuemysql> update helei set context=JSON_REMOVE(context,'$.name') where id=1;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from helei;+----+------------------------------------+| id | context                            |+----+------------------------------------+|  1 | {"age": 100, "address": "beijing"} ||  2 | {"age": 30, "name": "陈加持"}      ||  3 | {"age": 28, "name": "于浩"}        |+----+------------------------------------+3 rows in set ( sec)删除Key-Value

JSON格式存储BLOB的测试

Part1:Dynamic Columns处理方式的异同

①的动态列JSON格式存储

mysql> insert into helei_blob values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');Query OK, 3 rows affected ( sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from helei_blob;+----+-------------------------------+| id | blob_col                      |+----+-------------------------------+|  1 | {"name":"贺磊","age":100}     ||  2 | {"name":"陈加持","age":30}    ||  3 | {"name":"于浩","age":28}      |+----+-------------------------------+3 rows in set ( sec)

②MariaDB的动态列JSON格式存储

MariaDB [helei]> create table helei (id int(10) unsigned NOT NULL,context json default null,primary key(id));ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json default null,primary key(id))' at line 1可以看到MariaDB并不能直接存储JSON类型。MariaDB [helei]> show create table helei_blob\G;*************************** 1. row ***************************       Table: helei_blobCreate Table: CREATE TABLE `helei_blob` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `blob_col` blob,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set ( sec)ERROR: No query specifiedMariaDB [helei]> insert into helei_blob values(5,column_create('color','blue','size','XL'));Query OK, 1 row affected ( sec)MariaDB [helei]> select * from helei_blob;+----+--------------------------------+| id | blob_col                       |+----+--------------------------------+|  1 | {"name":"贺磊","age":100}      ||  2 | {"name":"陈加持","age":30}     ||  3 | {"name":"于浩","age":28}       ||  5 |       3 sizecolor!XL!blue |+----+--------------------------------+4 rows in set ( sec)直接查询是乱码需用以下函数查询MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id =5;+----+------------------------------+| id | column_json(blob_col)        |+----+------------------------------+|  5 | {"size":"XL","color":"blue"} |+----+------------------------------+1 row in set ( sec)MariaDB [helei]> select id,column_list(blob_col) from helei_blob where id =5;+----+-----------------------+| id | column_list(blob_col) |+----+-----------------------+|  5 | `size`,`color`        |+----+-----------------------+1 row in set ( sec)获取全部KeyMariaDB [helei]> select id,column_get(blob_col,'color' as char) as color from helei_blob where id =5;+----+-------+| id | color |+----+-------+|  5 | blue  |+----+-------+1 row in set ( sec)获取Key-ValueMariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'sex','man') where id=5;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;+----+------------------------------------------+| id | column_json(blob_col)                    |+----+------------------------------------------+|  5 | {"sex":"man","size":"XL","color":"blue"} |+----+------------------------------------------+1 row in set ( sec)增加Key-ValueMariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'color','black') where id=5;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;+----+-------------------------------------------+| id | column_json(blob_col)                     |+----+-------------------------------------------+|  5 | {"sex":"man","size":"XL","color":"black"} |+----+-------------------------------------------+1 row in set ( sec)更改Key-ValueMariaDB [helei]> update helei_blob set blob_col=column_delete(blob_col,'sex','man') where id=5;Query OK, 1 row affected ( sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;+----+-------------------------------+| id | column_json(blob_col)         |+----+-------------------------------+|  5 | {"size":"XL","color":"black"} |+----+-------------------------------+1 row in set ( sec)删除Key-Value

——总结——

虽然和/版本对于JSON的支持是比较完整的,不过MongoDB的Sharding功能更加好用,个人更倾向于MongoDB。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。