MySQL 支持原生 JSON 数据类型，可以有效访问 JSON（JavaScript Object Notation）中的数据。与将JSON 格式的字符串存储在字符串列中相比，JSON 数据类型具有以下优势：

1.存储在 JSON 列中的 JSON 文档被转换为允许对文档元素进行快速读取访问的内部格式。

2.Mysql提供自动验证存储在 JSON 列中的 JSON数据。

下面我们将通过创建具体的实例实现Json数据的操作。

首先我们创建表结构：

mysql> CREATE TABLE `student` (

-> `student_id` int(20) NOT NULL,

-> `name` varchar(255) DEFAULT NULL,

-> `age` int(10) DEFAULT NULL,

-> `json_value` json DEFAULT NULL,

-> PRIMARY KEY (`student_id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据：

insert into student VALUES(1,'张三',20,'{"className": "一年级", "classId": "1", "classLeaderId": "100"}');

insert into student VALUES(2,'李四',20,'{"className": "一年级", "classId": "1", "classLeaderId": "100"}');

insert into student VALUES(3,'王五',20,'{"className": "二年级", "classId": "2", "classLeaderId": "101"}');

查询结果：

上述入库操作时，我们是将拼接好的json字符串直接当作value值插入，很多读者可能会问“创建一个长度稍大点的字符串也可以实现？”。当然Mysql给我们的功能远远不止这些，下面我们将整理关于操作json的一些sql操作，以备大家学习参考。

函数

如何创建json

1.JSON_ARRAY(val1,val2,val3...)

该方法可以接受0个或者多个值得列表并返回一个包含这些值得 JSON 数组，结合设计我们可以利用该函数实现将List数组存储到数据库中。

2.JSON_OBJECT(key1,val1,key2,val2...)

该方法接受0个或者多个键值对列表并返回一个包含这些对的 JSON 对象，利用该方法我们可以很容易地创建一个json对象。

3.JSON_QUOTE(json_val)

通常用于生成有效的 JSON 字符串文字以包含在 JSON 文档中。

查询json

1.JSON_CONTAINS(json_doc, val[, path])

该方法可以查询JSON格式数据中是否包含特定对象，包含返回1，否则返回0。比如我们要查询年级为一年级的学生信息：

2.JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

该方法查询是json类型是否存在指定的key，存在则返回1，否则返回0。如果有参数为NULL，则返回NULL。one_or_all只能取值"one"或"all"，one表示只要有一个存在即可；all表示所有的都存在才行。例如查询json中是否包含className和classLeaderId。

3.JSON_EXTRACT(json_doc, path[, path] ...)

从json里抽取指定数据。如果有参数有NULL或接送不存在，则返回NULL。例如查询存在json中的年级名称。

4.column->path

JSON_EXTRACT的缩写方式，通过’$.’指定json中的值。例如查询json中的年级名称。

5.json_keys

获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。比如查询student中json_value的全部keys。

6.JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

用于在json格式中查询并返回符合条件的节点。如果有参数为NUL或path不存在，则返回NULL。one_or_all："one"表示查询到一个即返回；"all"表示查询所有。search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。path：在指定名称。比如查询值为“一年级”在json中的位置。

修改json

1.JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

在指定数组尾部追加取值，用于json数组。如果指定path是一个json对象时，则将其封装成一个json数组再追加。如果有参数为NULL，则返回NULL。比如在json数组'["1", ["2", "2.1"], "3"]'后添加2.2。

2.JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

在指定的json数组元素插入值，原位置及以右的元素顺次右移。如果指定的元素下标超过json数组的长度，则插入尾部。比如在json数组'["1", ["2", "2.1"], "3"]'后插入2.05到2与2.1之间。

3.JSON_INSERT(json_doc, path, val[, path, val] ...)

在指定路径下插入数据，如果路径已存在，则忽略此值（不存在才插入）。比如在student表中添加location:北京。

4.JSON_REPLACE(json_doc, path, val[, path, val] ...)

替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。例如替换student表json_value中的年级。

5.JSON_SET(json_doc, path, val[, path, val] ...)

设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。比如将className设置成“五年级”再补充country为“中国”。

6.JSON_MERGE(json_doc, json_doc[, json_doc] ...)

将多个json类型的数据合并，如果都是json数组，则结果自动合并为一个json数组；

如果都是json对象，则结果自动合并为一个json对象；如果有多种类型，则统一转换成json数组再进行合并。例如将[1,2]数组与对象{"name": "张三"}进行合并。

7.JSON_REMOVE(json_doc, path[, path] ...)

移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。例如删除student表中的classLeaderId。

json属性

1.json_depth

获取json的深度。如果参数为NULL，则返回NULL。

2.json_length

获取指定路径下的长度。如果参数为NULL，则返回NULL。

3.json_type

获取json的具体类型。如果参数为NULL，则返回NULL。

4.json_valid

判断输入值是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。

**文末福利——推荐一个《Python自动化测试学习交流群》给大家：

请关注+私信回复："头条" 就可以免费拿到软件测试学习资料，同时进入群学习交流~~