mysql5.7原生json

mysql5.7原生json

本文主要向大家介绍了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 changedmysql> 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: 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 (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: 0mysql> 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-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 (0.00 sec)获取全部Keymysql> 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: 0mysql> 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-Valuemysql> 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: 0mysql> 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-valuemysql> 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: 0mysql> 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数据库频道！