MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录_Javascript

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

一、问题背景

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

[
  {"id":"1905555466980773889","haspermission":true},
  {"id":"1905547884060835841","haspermission":false}
]

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

二、mysql json 函数

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

2.1 常用 json 函数

json_contains(target, candidate[, path]): 检查 json 文档是否包含特定值
json_extract(json_doc, path): 从 json 文档中提取值
json_object(key, val[, key, val]...): 创建 json 对象
json_array(val[, val]...): 创建 json 数组
json_valid(json_doc): 验证字符串是否为有效的 json

三、查询示例

3.1 基本查询

查询 json 数组中包含特定 id 的记录：

select * from sys_user
where 
  app_ids is not null
  and app_ids != ''
  and json_contains(app_ids, json_object('id', '1905555466980773889'));

3.2 查询多个 id

select * from sys_user
where 
  app_ids is not null
  and app_ids != ''
  and (
    json_contains(app_ids, json_object('id', '1905555466980773889'))
    or json_contains(app_ids, json_object('id', '1905547884060835841'))
  );

3.3 使用 json_overlaps (mysql 8.0+)

select * from sys_user
where 
  app_ids is not null
  and app_ids != ''
  and json_overlaps(
    app_ids, 
    json_array(
      json_object('id', '1905555466980773889'),
      json_object('id', '1905547884060835841')
    )
  );

3.4 查询特定权限的记录

select * from sys_user
where 
  app_ids is not null
  and app_ids != ''
  and json_contains(
    app_ids, 
    json_object('id', '1905555466980773889', 'haspermission', true)
  );

四、避免常见错误

4.1 空值处理

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

-- 错误做法
select * from sys_user where json_contains(app_ids, json_object('id', '123'));
-- 正确做法
select * from sys_user 
where 
  app_ids is not null 
  and app_ids != '' 
  and json_contains(app_ids, json_object('id', '123'));

4.2 json 格式匹配

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

-- 错误做法 (直接传入 id 字符串)
select * from sys_user where json_contains(app_ids, '"1905555466980773889"');
-- 正确做法 (创建与数组元素匹配的对象)
select * from sys_user where json_contains(app_ids, json_object('id', '1905555466980773889'));

4.3 确保 json 有效性

添加 json_valid 检查确保字段内容是有效的 json：

select * from sys_user
where 
  app_ids is not null
  and app_ids != ''
  and json_valid(app_ids) = 1
  and json_contains(app_ids, json_object('id', '1905555466980773889'));

五、在 mybatis plus 中的应用

5.1 基本查询

lambdaquerywrapper<sysuser> querywrapper = new lambdaquerywrapper<>();
querywrapper.isnotnull(sysuser::getappids)
            .ne(sysuser::getappids, "")
            .apply("json_contains(app_ids, json_object('id', {0}))", "1905555466980773889");
list<sysuser> userlist = sysusermapper.selectlist(querywrapper);

5.2 多条件查询

querywrapper<sysuser> querywrapper = new querywrapper<>();
querywrapper.isnotnull("app_ids")
            .ne("app_ids", "")
            .apply("json_valid(app_ids) = 1")
            .apply("json_contains(app_ids, json_object('id', {0}))", "1905555466980773889");
// 如果还要根据权限过滤
querywrapper.apply("json_contains(app_ids, json_object('id', {0}, 'haspermission', {1}))", 
                   "1905555466980773889", true);

5.3 查询多个 id

lambdaquerywrapper<sysuser> querywrapper = new lambdaquerywrapper<>();
querywrapper.isnotnull(sysuser::getappids)
            .ne(sysuser::getappids, "")
            .and(w -> w.apply("json_contains(app_ids, json_object('id', {0}))", "1905555466980773889")
                      .or()
                      .apply("json_contains(app_ids, json_object('id', {0}))", "1905547884060835841"));
list<sysuser> userlist = sysusermapper.selectlist(querywrapper);

六、性能优化建议

考虑使用 json 类型：如果您的 mysql 版本是 5.7+，考虑使用原生 json 类型代替 varchar，这样可以获得更好的性能和功能支持。

添加索引：虽然无法直接为 json 内容创建索引，但可以使用生成的列和函数索引：

alter table sys_user add column app_id_extracted json 
  generated always as (json_extract(app_ids, '$[*].id')) virtual;
alter table sys_user add index idx_app_id_extracted (app_id_extracted);

定期维护：对于大表，定期 optimize table 有助于维护性能。