mysql 之json字段详解(多层复杂检索)
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=1JSON_REPLACE
替换/修改JSON文档已经存在的值。
语法：
JSON_REPLACE(json_doc, path, val[, path, val] ...)
update users set info=JSON_REPLACE(info,'$.name',"n113") WHERE id=1JSON_MODIFY
来更新一个属性，无论它是否存在
语法：
update users set info=JSON_MODIFY(info,'$.name',"n1134",true) WHERE id=1JSON_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_37583655/article/details/119889086
https://blog.csdn.net/wzy0623/article/details/139678967
https://dev.mysql.com/doc/refman/8.4/en/json-function-reference.html