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

2025-04-14 16:50
文章标签 mysql varchar json 查询 记录 类型 数据 问题 database

本文主要是介绍MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V...

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

一、问题背景

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

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

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

二、MySQL JSON 函数

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

2.1 常用 JSON 函China编程数

  • 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_idsandroid 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', '19055554669javascript80773889'));

五、在 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 有助于维护性能。

七、总结

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

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

正确使用这些技术可以有效地查询和处理 VARCHAR 中存储的 JSON 数据。

到此这篇关于MySQL 中查询 VARCHAR 类型 JSON 数据的的文章就介绍到这了,更多相关mysql 查询 VARCHAR 类型 JSON 数据内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！

这篇关于MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1154211。

相关文章

MySQL 分区与分库分表策略应用小结

MySQL 分区与分库分表策略应用小结

《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如... 目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念
阅读更多...
MySQL高级查询之JOIN、子查询、窗口函数实际案例

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》：本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN（连接查询）1.1 内连接（INNER JOIN）1.2 左连接（LEFT JOI
阅读更多...
SpringBatch数据写入实现

SpringBatch数据写入实现

《SpringBatch数据写入实现》SpringBatch通过ItemWriter接口及其丰富的实现,提供了强大的数据写入能力,本文主要介绍了SpringBatch数据写入实现,具有一定的参考价值,... 目录python引言一、ItemWriter核心概念二、数据库写入实现三、文件写入实现四、多目标写入
阅读更多...
MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

《MySQL中动态生成SQL语句去掉所有字段的空格的操作方法》在数据库管理过程中,我们常常会遇到需要对表中字段进行清洗和整理的情况,本文将详细介绍如何在MySQL中动态生成SQL语句来去掉所有字段的空... 目录在mysql中动态生成SQL语句去掉所有字段的空格准备工作原理分析动态生成SQL语句在MySQL
阅读更多...
MySQL中FIND_IN_SET函数与INSTR函数用法解析

MySQL中FIND_IN_SET函数与INSTR函数用法解析

《MySQL中FIND_IN_SET函数与INSTR函数用法解析》：本文主要介绍MySQL中FIND_IN_SET函数与INSTR函数用法解析,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一... 目录一、功能定义与语法1、FIND_IN_SET函数2、INSTR函数二、本质区别对比三、实际场景案例分
阅读更多...
使用Python将JSON,XML和YAML数据写入Excel文件

使用Python将JSON,XML和YAML数据写入Excel文件

《使用Python将JSON,XML和YAML数据写入Excel文件》JSON、XML和YAML作为主流结构化数据格式,因其层次化表达能力和跨平台兼容性,已成为系统间数据交换的通用载体,本文将介绍如何... 目录如何使用python写入数据到Excel工作表用Python导入jsON数据到Excel工作表用
阅读更多...
MySQL中的交叉连接、自然连接和内连接查询详解

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》：本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接（cross join）三、自然连接（naturalandroid join）四
阅读更多...
Pyserial设置缓冲区大小失败的问题解决

Pyserial设置缓冲区大小失败的问题解决

《Pyserial设置缓冲区大小失败的问题解决》本文主要介绍了Pyserial设置缓冲区大小失败的问题解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面... 目录问题描述原因分析解决方案问题描述使用set_buffer_size()设置缓冲区大小后，buf
阅读更多...
Mysql如何将数据按照年月分组的统计

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》：本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA
阅读更多...
Mysql表如何按照日期字段的年月分区

Mysql表如何按照日期字段的年月分区

《Mysql表如何按照日期字段的年月分区》：本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2