mysql 之json字段详解（多层复杂检索）

大家好，欢迎来到IT知识分享网。

MySQL 5.7.8 开始支持JSON 数据类型。MySQL 8.0版本中增加了对JSON类型的索引支持。

示例表

CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `info` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB

插入数据

INSERT INTO `users`(`info`) VALUES('{"name": "n1", "param": {"image": "1.gif"}, "version": [{"id": 1}, {"id": 2}]}'); INSERT INTO `users`(`info`) VALUES('[{"name":"n2","param":{"image":"2.gif"},"version":[{"id":33},{"id":44}]}]'); INSERT INTO `users`(`info`) VALUES('{"name":"n2","ids":[55,66]}');

json查询函数

JSON_CONTAINS

是 MySQL 提供的一个 JSON 函数，用于测试一个 JSON 文档是否包含特定的值。如果包含则返回 1，否则返回 0。

函数的语法如下：

JSON_CONTAINS(target, candidate[, path])

1. target: 待搜索的目标 JSON 文档。

2. candidate: 在目标 JSON 文档中要搜索的值。

3. path（可选）： 路径表达式，指示在哪里搜索候选值。

示例：

SELECT * FROM users WHERE JSON_CONTAINS(info->'$.name','"n1"')

结果查询json字段中 第一name为n1数据

使用path参数

SELECT * FROM users WHERE JSON_CONTAINS(info,'"n1"','$.name')；

结果也为

SELECT JSON_CONTAINS(info,'"n1"','$.name') FROM users;

结果

JSON_EXTRACT

返回 JSON 文档中的数据，该数据是从路径参数匹配的文档部分中选择的。如果任何参数为 NULL 或在文档路径中没有找到值，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，则会发生错误。

语法：

JSON_EXTRACT(json_doc, path[, path] …)

SELECT * FROM users WHERE JSON_EXTRACT(info,'$.name')="n1";

结果

MySQL 5.7+，你也可以使用->或->>操作符作为JSON_EXTRACT

即

SELECT * FROM users WHERE info->'$.name'="n1";

返回json字段里面的某个值

SELECT info->'$.name' FROM users;

结果：

JSON_KEYS

以 JSON 数组的形式返回 JSON 对象的顶级键。或者，如果给定了路径参数，则返回所选路径中的顶级键。如果任何参数为 NULL、json_doc 参数不是对象，或者给定路径未找到对象，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者路径参数不是有效路径表达式，或者包含 * 或 通配符，则会发生错误。

语法：

JSON_KEYS(json_doc[, path])

查询json所有的key

SELECT JSON_KEYS(info) FROM users;

结果

默认值返回一级的key值

如果想查询下一级，可以指定path

SELECT JSON_KEYS(info,'$.param') FROM users;

如果是数组，则需指定下标

SELECT JSON_KEYS(info,'$.version[0]') FROM users;

JSON_OBJECT

创建json对象，评估键值对的列表（可能为空），并返回包含这些对的 JSON 对象。如果任何键名为 NULL 或参数数为奇数，则会发生错误。

语法：

JSON_OBJECT([key, val[, key, val] …])

示例：

SELECT JSON_OBJECT('id','1')

复杂检索

匹配对象里数组值

SELECT * FROM users WHERE JSON_CONTAINS(info->'$.ids','55') LIMIT 100;

检索带有key值的数据

SELECT * FROM users WHERE JSON_CONTAINS(info->'$.version',JSON_OBJECT("id",1));

多级对象值查找

SELECT * FROM users WHERE info->'$.param.image'="1.gif" LIMIT 100;

查找数组对象值

SELECT * FROM users where JSON_CONTAINS(info,JSON_OBJECT('name', "n2"));

查找多维数组

SELECT * FROM users where JSON_CONTAINS(info,JSON_OBJECT('version', JSON_OBJECT('id', 33)));

更新json数据

JSON_SET

json_set函数支持更新包括标量值和其他JSON文档在内的所有JSON数据类型

语法：

JSON_SET(json_object, path, val[, path, val] …)

示例

update users set info=JSON_SET(info,'$.name',"n11") WHERE id=1;

更新数组

update users set info=JSON_SET(info,'$[0].name',"n11") WHERE id=1

JSON_REPLACE

替换/修改JSON文档已经存在的值。

语法：

JSON_REPLACE(json_doc, path, val[, path, val] …)

update users set info=JSON_REPLACE(info,'$.name',"n113") WHERE id=1

JSON_MODIFY

来更新一个属性，无论它是否存在

语法：

update users set info=JSON_MODIFY(info,'$.name',"n1134",true) WHERE id=1

JSON_MODIFY的最后一个参数指定是否在修改时忽略不存在的属性，如果设置为true，则会添加新属性或更新现有属性。

注意：JSON_MODIFY是 MySQL 8 及以上版本支持的函数。如果你的 MySQL 版本较低，可能就不存在这个函数。

JSON_INSERT

新增不存在的值。

语法

JSON_INSERT(json_doc, path, val[, path, val] …)

示例

update users set info=JSON_INSERT(info,'$.namenew',"new1") WHERE id=1;

参考：

https://blog.csdn.net/m0_/article/details/

https://blog.csdn.net/wzy0623/article/details/

https://dev.mysql.com/doc/refman/8.4/en/json-function-reference.html