Mysql JSON对象和JSON数组查询

file_type可以是 varchar，也可以是 json类型

1. JSON对象

{"key": 1, "name": "万飞"}
1

查询

SELECT * FROM `ak_file_config` where file_type -> '$.name' = "万飞"
1

2. 字符串JSON数组

["EXE", "白加黑", "DLL"]
1

查询

SELECT * FROM `ak_file_config` where JSON_CONTAINS(file_type, JSON_ARRAY("白加黑","DLL"))
1

3. 对象数组

参考 https://wenku.baidu.com/view/0831b7cc6194dd88d0d233d4b14e852459fb3958?aggId=0831b7cc6194dd88d0d233d4b14e852459fb3958

4. 集成 Mybatis plus

// jsonArray查询
.apply(CollUtil.isNotEmpty(query.getFileType()), StrUtil.format("JSON_CONTAINS(t.file_type, JSON_ARRAY({}))",
                // 设置占位符{0},{1},{2}
                IntStream.range(0, Optional.ofNullable(query.getFileType()).orElse(Collections.emptyList()).size())
                        .mapToObj(i -> "{".concat(String.valueOf(i)).concat("}"))
                        .collect(Collectors.joining(","))),
        Optional.ofNullable(query.getFileType()).orElse(Collections.emptyList()).toArray())
1
2
3
4
5
6
7

参考 https://blog.csdn.net/qq_31832209/article/details/125374325