Mysql处理json的一些总结

李雅普是诺夫

已于 2023-08-11 17:30:05 修改

阅读量1.4k

点赞数

文章标签： mysql json

于 2023-08-11 17:28:39 首次发布

本文链接：https://blog.csdn.net/lypsnf/article/details/132208216

版权

一、字段

mysql中有专门的json字段,但我们也可以手动指定varchar等字符串类字段中存入json,二者在查询时语法没有区别,但json字段会对增改语句进行校验,如果存入或修改的内容不是json格式,sql语句会直接报错不执行,而varchar类字段肯定不会

二、查询语法

示例数据库表如下,form字段中存储了json格式的数据

1.JSON_EXTRACT()函数和 "->"

这两的效果是一样的,都是从json中取出指定key的value值,并保留双引号

其中form是表中字段名,'${key名}'是固定格式,如果key不存在则返回Null

SELECT form->'$.key' FROM `json_test`

SELECT JSON_EXTRACT(form,'$.key') FROM `json_test`

2.JSON_UNQUOTE()函数

上述方法在查询之后会保留内容无关的双引号,可以使用JSON_UNQUOTE()函数去除

SELECT JSON_UNQUOTE(JSON_EXTRACT(form,'$.key')) FROM `json_test` where id = 1

3. "->>"

等价于JSON_UNQUOTE(JSON_EXTRACT())这种嵌套的格式,在使用上"->>"可以更直接的拿到想要的结果,是我们处理json最常用的方法

SELECT form->>'$.key' FROM `json_test` where id = 1

三、特殊场景

1.数组

在示例表中,第一条数据的"list"存了一个数组,如果用" form->>'$.list' "查询会返回整个数组,没有办法精确定位到指定下标的内容,解决办法:

①可以直接在数组后追加下标" [index] " (从0开始),这种方式比较简洁直观,但是可扩展性相对较差;

SELECT form->>'$.list[0]' FROM `json_test` where id = 1

②在JSON_EXTRACT()函数中引用数组下标 '$.[index]'(从0开始)

具体形式为JSON_EXTRACT({json数据},'$.[index]') ,结构稍复杂但可扩展性更强

SELECT JSON_UNQUOTE(JSON_EXTRACT(form->>'$.list','$[0]')) 
FROM `json_test` where id = 1

2.数组中包对象

示例数据表中第二行即为json数组包对象的例子,解决办法和上面的数组类似:

①直接追加对象属性

select form->>'$.listObj[0].eName'
from `json_test` where id = 2

②利用JSON_EXTRACT()函数层层拆解

select JSON_UNQUOTE(JSON_EXTRACT(form->>'$.listObj','$[0].eName')) 
from `json_test` where id = 2

3.遍历json数组

mysql5.7本身是不支持递归查询的,这里介绍一种特殊的方法做一个伪递归

大体思路是做一个有限的循环,类似于for-i的形式,每次循环都与json数组的长度进行比较,次数大于数组长度就退出循环,以示例表中的第二行数据为例

select JSON_UNQUOTE(JSON_EXTRACT(jt.form->> '$.listObj', CONCAT('$[', Numbers.N - 1, '].eName')))
from `json_test` jt
LEFT JOIN 
(
    SELECT @row := @row + 1 AS N 
    FROM 
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1, 
    (SELECT @row:=0) T0
)Numbers 
ON Numbers.N <= JSON_LENGTH(jt.form -> '$.listObj')
where jt.id = 2

T1,T2表中各10个数据,循环次数即为10*10=100次,可手动更改

将要取出的数据下标通过变量的形式CONCAT拼接,实现动态查询

4.混合数据

如果很不幸在varchar类字段中既存了普通字符串,又存了json类型...常见于兼容老数据,此时直接通过"->>"或JSON_EXTRACT()调用会报错,需要额外加一个条件判断

可以借助JSON_VALID()函数判断该字段中的内容是否为json数据

select 
case when JSON_VALID(form->> '$.listObj') then
form->> '$.listObj[0].eName' 
else form->> '$.listObj' end
from `json_test` where id = 2

上述sql大意为先判断form字段中的listObj是否为json格式,如是则返回下标为0的对象中的eName属性,不是就返回整个listObj

四、总结

json格式的最大意义在于数据格式、数量的灵活可变性,可以让表中的字段进行动态的扩容,这对于传统ORM框架下的操作是十分友好的,不再需要追加一个字段而动整张表的结构(在mybatis框架下还需要修改java中对应的实体类),当然json字段也有其局限性,首先是索引建立相对麻烦,而且字段内容多样性会导致索引效率下降;二来java中解析该字段也要额外付出精力,需要考虑强转后的类型,数据兼容等问题;总的来说还是需要以具体业务做考量

更多细节可参照官方文档:https://dev.mysql.com/doc/refman/5.7/en/create-index.html