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

100人浏览 2024-08-11 13:02:25

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中的位置。