你知道吗?MySQL可以直接操作JSON

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。

加我VX:atstudy-js 回复“测试”,进入 自动化测试学习交流群~ 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值