在数据库设计中，有时我们会将 JSON 数据存储在 VARCHAR 或 TEXT 类型字段中。这种方式虽然灵活，但在查询时需要特别注意。本文将详细介绍如何在 MySQL 中有效查询存储为 VARCHAR 类型的 JSON 数据。

一、问题背景

当 JSON 数据存储在 VARCHAR 列中时，常见的数据格式如下：

1. [
   
2.   {"id":"1905555466980773889","hasPermission":true},
   
3.   {"id":"1905547884060835841","hasPermission":false}
   
4. ]

复制代码

我们需要查询这个 JSON 数组中是否包含特定 ID 的对象。

二、MySQL JSON 函数

MySQL 5.7+ 版本提供了丰富的 JSON 处理函数，即使数据类型是 VARCHAR，只要内容是有效的 JSON，我们仍然可以使用这些函数：

2.1 常用 JSON 函数

• 1. JSON_CONTAINS(target, candidate[, path])

  复制代码
  : 检查 JSON 文档是否包含特定值
  
• 1. JSON_EXTRACT(json_doc, path)

  复制代码
  : 从 JSON 文档中提取值
  
• 1. JSON_OBJECT(key, val[, key, val]...)

  复制代码
  : 创建 JSON 对象
  
• 1. JSON_ARRAY(val[, val]...)

  复制代码
  : 创建 JSON 数组
  
• 1. JSON_VALID(json_doc)

  复制代码
  : 验证字符串是否为有效的 JSON
  

三、查询示例


3.1 基本查询

查询 JSON 数组中包含特定 ID 的记录：

1. SELECT * FROM sys_user
   
2. WHERE
   
3.   app_ids IS NOT NULL
   
4.   AND app_ids != ''
   
5.   AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

复制代码

3.2 查询多个 ID

1. SELECT * FROM sys_user
   
2. WHERE
   
3.   app_ids IS NOT NULL
   
4.   AND app_ids != ''
   
5.   AND (
   
6.     JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'))
   
7.     OR JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905547884060835841'))
   
8.   );

复制代码

3.3 使用 JSON_OVERLAPS (MySQL 8.0+)

1. SELECT * FROM sys_user
   
2. WHERE
   
3.   app_ids IS NOT NULL
   
4.   AND app_ids != ''
   
5.   AND JSON_OVERLAPS(
   
6.     app_ids,
   
7.     JSON_ARRAY(
   
8.       JSON_OBJECT('id', '1905555466980773889'),
   
9.       JSON_OBJECT('id', '1905547884060835841')
   
10.     )
    
11.   );

复制代码

3.4 查询特定权限的记录

1. SELECT * FROM sys_user
   
2. WHERE
   
3.   app_ids IS NOT NULL
   
4.   AND app_ids != ''
   
5.   AND JSON_CONTAINS(
   
6.     app_ids,
   
7.     JSON_OBJECT('id', '1905555466980773889', 'hasPermission', true)
   
8.   );

复制代码

四、避免常见错误


4.1 空值处理

JSON_CONTAINS 函数在处理 NULL 或空字符串时会报错，所以需要先排除这些情况：

1. -- 错误做法
   
2. SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));
   
3. -- 正确做法
   
4. SELECT * FROM sys_user
   
5. WHERE
   
6.   app_ids IS NOT NULL
   
7.   AND app_ids != ''
   
8.   AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '123'));

复制代码

4.2 JSON 格式匹配

确保 JSON_CONTAINS 的第二个参数结构与目标 JSON 中的结构匹配：

1. -- 错误做法 (直接传入 ID 字符串)
   
2. SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, '"1905555466980773889"');
   
3. -- 正确做法 (创建与数组元素匹配的对象)
   
4. SELECT * FROM sys_user WHERE JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

复制代码

4.3 确保 JSON 有效性

添加 JSON_VALID 检查确保字段内容是有效的 JSON：

1. SELECT * FROM sys_user
   
2. WHERE
   
3.   app_ids IS NOT NULL
   
4.   AND app_ids != ''
   
5.   AND JSON_VALID(app_ids) = 1
   
6.   AND JSON_CONTAINS(app_ids, JSON_OBJECT('id', '1905555466980773889'));

复制代码

五、在 MyBatis Plus 中的应用


5.1 基本查询

1. LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
   
2. queryWrapper.isNotNull(SysUser::getAppIds)
   
3.             .ne(SysUser::getAppIds, "")
   
4.             .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
   
5. List<SysUser> userList = sysUserMapper.selectList(queryWrapper);

复制代码

5.2 多条件查询

1. QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
   
2. queryWrapper.isNotNull("app_ids")
   
3.             .ne("app_ids", "")
   
4.             .apply("JSON_VALID(app_ids) = 1")
   
5.             .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889");
   
6. // 如果还要根据权限过滤
   
7. queryWrapper.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}, 'hasPermission', {1}))",
   
8.                    "1905555466980773889", true);

复制代码

5.3 查询多个 ID

1. LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
   
2. queryWrapper.isNotNull(SysUser::getAppIds)
   
3.             .ne(SysUser::getAppIds, "")
   
4.             .and(w -> w.apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905555466980773889")
   
5.                       .or()
   
6.                       .apply("JSON_CONTAINS(app_ids, JSON_OBJECT('id', {0}))", "1905547884060835841"));
   
7. List<SysUser> userList = sysUserMapper.selectList(queryWrapper);

复制代码

六、性能优化建议

考虑使用 JSON 类型：如果您的 MySQL 版本是 5.7+，考虑使用原生 JSON 类型代替 VARCHAR，这样可以获得更好的性能和功能支持。
添加索引：虽然无法直接为 JSON 内容创建索引，但可以使用生成的列和函数索引：

1. ALTER TABLE sys_user ADD COLUMN app_id_extracted JSON
   
2.   GENERATED ALWAYS AS (JSON_EXTRACT(app_ids, '$[*].id')) VIRTUAL;
   
3. ALTER TABLE sys_user ADD INDEX idx_app_id_extracted (app_id_extracted);

复制代码

定期维护：对于大表，定期 OPTIMIZE TABLE 有助于维护性能。

七、总结

在 MySQL 中查询 VARCHAR 类型的 JSON 数据时，关键是：

• 使用 JSON_CONTAINS 函数并构造正确的 JSON 结构进行匹配
  
• 处理好 NULL 和空字符串
  
• 验证 JSON 有效性
  
• 在 MyBatis Plus 中使用 apply 方法添加原生 SQL 条件
  

正确使用这些技术可以有效地查询和处理 VARCHAR 中存储的 JSON 数据。
到此这篇关于MySQL 中查询 VARCHAR 类型 JSON 数据的的文章就介绍到这了,更多相关mysql 查询 VARCHAR 类型 JSON 数据内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源：https://www.jb51.net/database/339547qe5.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！