本文主要向大家介绍了MySQL数据库之10分钟了解MySQL5.7对原生JSON的支持与用法 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

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;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | NO   | PRI | NULL    |       |
| context | json             | YES  |     | 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

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！