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。

作者：梁兵兵   

来源：http://www.51testing.com/html/13/n-4479913.html