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

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

标签：

MySQL

wKioL1ftzemTuZrlAAA4eVx2Dz8486.jpg

Part1:JSON格式的支持

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

Part2:创建相应表结构

[root@HE3 ~]# mysql -V

mysql Ver 14.14 Distrib 5.7.15, for linux-glibc2.5 (x86_64) using EditLine wrapper

mysql> create database helei;

Query OK, 1 row affected (0.00 sec)

mysql> use helei;

Database changed

mysql> create table helei (id int(10) unsigned NOT NULL,context json default null,primary key(id));

Query OK, 0 rows affected (0.02 sec)

mysql> show create table helei \G

*************************** 1. row ***************************

Table: helei

Create Table: CREATE TABLE `helei` (

`id` int(10) unsigned NOT NULL,

`context` json DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.02 sec)

Part3:构造数据&测试

mysql> desc helei;

+---------+------------------+------+-----+---------+-------+

+---------+------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

+---------+------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> insert into helei values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from helei;

+----+----------------------------------+

| id | context |

+----+----------------------------------+

| 1 | {"age": 100, "name": "贺磊"} |

| 2 | {"age": 30, "name": "陈加持"} |

| 3 | {"age": 28, "name": "于浩"} |

+----+----------------------------------+

3 rows in set (0.00 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 (0.00 sec)

获取Key-Value

mysql> select id,json_keys(context) from helei;

+----+--------------------+

| id | json_keys(context) |

+----+--------------------+

| 1 | ["age", "name"] |

| 2 | ["age", "name"] |

| 3 | ["age", "name"] |

+----+--------------------+

3 rows in set (0.00 sec)

获取全部Key

mysql> update helei set context=JSON_INSERT(context,'$.name',"贺磊",'$.address','beijing')where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from helei;

+----+------------------------------------------------------+

| id | context |

+----+------------------------------------------------------+

| 1 | {"age": 100, "name": "贺磊", "address": "beijing"} |

| 2 | {"age": 30, "name": "陈加持"} |

| 3 | {"age": 28, "name": "于浩"} |

+----+------------------------------------------------------+

3 rows in set (0.00 sec)

增加Key-Value

mysql> update helei set context=JSON_SET(context,'$.name',"高穷帅")where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from helei;

+----+---------------------------------------------------------+

| id | context |

+----+---------------------------------------------------------+

| 1 | {"age": 100, "name": "高穷帅", "address": "beijing"} |

| 2 | {"age": 30, "name": "陈加持"} |

| 3 | {"age": 28, "name": "于浩"} |

+----+---------------------------------------------------------+

3 rows in set (0.00 sec)

变更key-value

mysql> update helei set context=JSON_REMOVE(context,'$.name') where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from helei;

+----+------------------------------------+

| id | context |

+----+------------------------------------+

| 1 | {"age": 100, "address": "beijing"} |

| 2 | {"age": 30, "name": "陈加持"} |

| 3 | {"age": 28, "name": "于浩"} |

+----+------------------------------------+

3 rows in set (0.00 sec)

删除Key-Value

JSON格式存储BLOB的测试

Part1:Dynamic Columns处理方式的异同

①MySQL5.7的动态列JSON格式存储

mysql> insert into helei_blob values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from helei_blob;

+----+-------------------------------+

| id | blob_col |

+----+-------------------------------+

| 1 | {"name":"贺磊","age":100} |

| 2 | {"name":"陈加持","age":30} |

| 3 | {"name":"于浩","age":28} |

+----+-------------------------------+

3 rows in set (0.00 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_blob

Create Table: CREATE TABLE `helei_blob` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`blob_col` blob,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [helei]> insert into helei_blob values(5,column_create('color','blue','size','XL'));

Query OK, 1 row affected (0.01 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 (0.00 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 (0.00 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 (0.00 sec)

获取全部Key

MariaDB [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 (0.00 sec)

获取Key-Value

MariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'sex','man') where id=5;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [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 (0.00 sec)

增加Key-Value

MariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'color','black') where id=5;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [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 (0.00 sec)

更改Key-Value

MariaDB [helei]> update helei_blob set blob_col=column_delete(blob_col,'sex','man') where id=5;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [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 (0.00 sec)

删除Key-Value

——总结——

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

mysqljsonMySQL

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

holdtom

手记
篇

粉丝

240

获赞与收藏

991

关注作者，订阅最新文章

阅读免费教程

MySQL 进阶教程

32个小节 49439 922

MySQL 入门教程

32个小节 124129 1538

后端通用面试教程

41个小节 28701 323

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

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

阅读免费教程