深入探索MySQL中JSON数据的查询、转换及springboot中的应用

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL版本引入了对JSON数据类型的支持,这为我们处理和存储非结构化数据提供了新的可能性。通过灵活利用MySQL的JSON函数,我们可以实现高效的查询和转换操作,提取有用的数据,并将其转换为有意义的格式。本文将深入探索MySQL中JSON数据的查询与转换技巧,帮助您更好地利用这一功能。

mysql-json.jpg
MySQL版本引入了对JSON数据类型的支持,这为我们处理和存储非结构化数据提供了新的可能性。通过灵活利用MySQL的JSON函数,我们可以实现高效的查询和转换操作,提取有用的数据,并将其转换为有意义的格式。本文将深入探索MySQL中JSON数据的查询与转换技巧,帮助您更好地利用这一功能。

使用

  1. 创建包含JSON字段的表 在MySQL中,我们可以使用JSON数据类型来定义表的字段。例如,我们可以创建一个名为jsontest的表,其中包含一个名为details的JSON字段,用于存储工单的信息。下面是创建这样一张表的示例SQL语句:
    ```sql
    CREATE TABLE jsontest (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    details JSON
    );

2. 插入JSON数据 一旦我们创建了包含JSON字段的表,就可以插入JSON格式的数据。例如,我们可以插入多条工单的详细信息:

```sql
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test1\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/7A/wKgKaWRjXhWALn38AAJ5ggV2TcU357.png\", \"jumpUrl\": \"\", \"jumpFlag\": 2}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test2\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/8A/wKgKZ2RjXcqACOgEAAJ5grWRHy4977.png\", \"jumpUrl\": \"http://192.168.10.105:8080/cnpc/material/homePage\", \"jumpFlag\": 1}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test3\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/7A/wKgKaWRjXmqAbM0IAAJ5ggV2TcU473.png\", \"jumpUrl\": \"\", \"jumpFlag\": 2}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test4\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/8A/wKgKZ2RjXh-AJeXRAAJ5grWRHy4787.png\", \"jumpUrl\": \"http://192.168.10.105:8080/cnpc/material/homePage\", \"jumpFlag\": 1}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test5\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/7A/wKgKaWRjXrSAVUHaAAJ5ggV2TcU051.png\", \"jumpUrl\": \"\", \"jumpFlag\": 2}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"xj-test6\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/8A/wKgKZ2RjY6aAFoTRAAJ5grWRHy4558.png\", \"jumpUrl\": \"\", \"jumpFlag\": 2}');
INSERT INTO `jsontest`(`details`) VALUES ('{\"title\": \"测试创建常见问题\", \"picUrl\": \"http://192.168.10.105:8080/group1/M00/12/8A/wKgKZ2RkN0-ABLD1AAQhAGCHXXo497.png\", \"jumpUrl\": \"www.baidu.com\", \"jumpFlag\": 1}');
  1. 查询JSON数据 MySQL提供了一系列强大的函数来查询和提取JSON数据。例如,我们可以使用JSON_EXTRACT()函数提取JSON字段中的特定值。以下是一个查询工单名称和图片的示例:
    SELECT
        JSON_EXTRACT( details, '$.title' ) AS title,
        JSON_EXTRACT( details, '$.picUrl' ) AS picUrl 
    FROM
    jsontest;

或者:

SELECT
    details -> '$.title' AS title,
    details -> '$.picUrl' AS picUrl 
FROM
    jsontest

结果:
_20230701211846.png

  1. 过滤和排序JSON数据 我们可以使用WHERE子句和ORDER BY子句来过滤和排序JSON字段中的数据。例如,我们可以查询工单名称包含xj的工单,并按照名称进行降序排序:
SELECT
    JSON_EXTRACT( details, '$.title' ) AS title,
    JSON_EXTRACT( details, '$.picUrl' ) AS picUrl 
FROM
    jsontest 
WHERE
    JSON_EXTRACT( details, '$.title' ) LIKE '%xj%' 
ORDER BY
    JSON_EXTRACT( details, '$.title' ) DESC;

或者

SELECT
    details -> '$.title' AS title,
    details -> '$.picUrl' AS picUrl 
FROM
    jsontest 
WHERE
    details -> '$.title' LIKE '%xj%' 
ORDER BY
    details -> '$.title' DESC;

结果:
_20230701212800.png

  1. 更新JSON数据 MySQL提供了函数来更新JSON字段中的数据。例如,我们可以使用JSON_SET()函数工单名称:
UPDATE jsontest 
SET details = JSON_SET( details, '$.title', 'xj-update1' ) 
WHERE
    id = 1;
  1. 删除JSON数据 类似于更新操作,我们也可以使用函数来删除JSON字段中的数据。例如,我们可以使用JSON_REMOVE()函数删除工单的调换类型:
UPDATE jsontest 
SET details = JSON_REMOVE( details, '$.jumpFlag' ) 
WHERE
    id = 1;
  1. JSON数据的转换 有时候,我们需要将JSON数据转换为其他格式,例如将JSON转换为表格形式。MySQL 提供了JSON_TABLE()函数,可以将JSON数据解析为关系型表。以下是一个将JSON数据转换为表格的示例:

SELECT
    info.* 
FROM
    jsontest,
    JSON_TABLE (
        details,
    '$' COLUMNS ( title VARCHAR ( 255 ) PATH '$.title', pic_url VARCHAR ( 255 ) PATH '$.picUrl', jump_url VARCHAR ( 255 ) PATH '$.jumpUrl' ) 
    ) AS info;

结果:
_20230702061925.png

springboot 中使用

  • 添加 JSONObjectTypeHandler类
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JSONObjectTypeHandler extends BaseTypeHandler<JSONObject> {
   
   

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
   
   
        ps.setString(i, parameter.toJSONString());
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
   
   
        String json = rs.getString(columnName);
        return StringUtils.isNotBlank(json) ? JSONObject.parseObject(json) : null;
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
   
   
        String json = rs.getString(columnIndex);
        return StringUtils.isNotBlank(json) ? JSONObject.parseObject(json) : null;
    }

    @Override
    public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
   
   
        String json = cs.getString(columnIndex);
        return StringUtils.isNotBlank(json) ? JSONObject.parseObject(json) : null;
    }
}
  • mybatis中使用

在mybatis-config.xml添加typeHandler

<typeHandlers>
    <typeHandler handler="cn.xj.framework.json.JSONObjectTypeHandler"/>
</typeHandlers>

resultMap 中添加typeHandler

    <resultMap type="InfoVo"     id="InfoVoResult">
        <id     property="id"                     column="id"                 />
        <result property="details"           column="details"
                typeHandler="cn.xj.framework.json.JSONObjectTypeHandler"    />
    </resultMap>
  • mybatis-plus 中使用

在实体类的字段上添加注解

  /**
   * 工单内容
   */
  @TableField(value = "details",typeHandler= JSONObjectTypeHandler.class)
  private JSONObject details;

总结

MySQL的JSON支持为我们处理和查询非结构化数据提供了强大的工具。通过使用JSON函数,我们可以轻松地查询和提取JSON字段中的数据,实现灵活的过滤和排序。同时,我们还可以利用JSON函数对JSON数据进行更新和删除操作,使得数据的维护更加方便。此外,MySQL还提供了JSON_TABLE()函数,可以将JSON数据转换为关系型表格形式,进一步扩展了数据处理的能力。

通过熟练掌握MySQL中JSON数据的查询与转换技巧,您可以更好地处理和利用非结构化数据,提高应用程序的性能和灵活性。无论是构建电子商务平台还是开展数据分析,MySQL 的JSON功能都将为您带来更多可能性和创新空间。

参考文献:

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8天前
|
Java 关系型数据库 MySQL
如何实现Springboot+camunda+mysql的集成
【7月更文挑战第2天】集成Spring Boot、Camunda和MySQL的简要步骤: 1. 初始化Spring Boot项目,添加Camunda和MySQL驱动依赖。 2. 配置`application.properties`,包括数据库URL、用户名和密码。 3. 设置Camunda引擎属性,指定数据源。 4. 引入流程定义文件(如`.bpmn`)。 5. 创建服务处理流程操作,创建控制器接收请求。 6. Camunda自动在数据库创建表结构。 7. 启动应用,测试流程启动,如通过服务和控制器开始流程实例。 示例代码包括服务类启动流程实例及控制器接口。实际集成需按业务需求调整。
|
5天前
|
JSON 分布式计算 大数据
MaxCompute产品使用合集之如何解析嵌套的JSON数据
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
8天前
|
存储 关系型数据库 MySQL
MySQL小白教程(进阶篇):数据管理与高级查询
MySQL小白教程(进阶篇):数据管理与高级查询
|
8天前
|
存储 关系型数据库 MySQL
MySQL小白教程:从入门到查询高手
MySQL小白教程:从入门到查询高手
|
12天前
|
JSON JavaScript 前端开发
技术心得:利用JsonSchema校验json数据内容的合规性
技术心得:利用JsonSchema校验json数据内容的合规性
12 0
|
18天前
|
SQL NoSQL 关系型数据库
若依修改02,若以提供了多种版本,RuoYi-Cloud和SpringBoot+Vue都是PC端的,如果想要适配手机端,用Uniapp+vue,导入Mysql和启动Redis
若依修改02,若以提供了多种版本,RuoYi-Cloud和SpringBoot+Vue都是PC端的,如果想要适配手机端,用Uniapp+vue,导入Mysql和启动Redis
|
18天前
|
JSON 数据格式
MysbatisPlus-核心功能-IService开发基础业务接口,MysbatisPlus_Restful风格,新增@RequestBody指定是为了接收Json数据的,使用swagger必须注解
MysbatisPlus-核心功能-IService开发基础业务接口,MysbatisPlus_Restful风格,新增@RequestBody指定是为了接收Json数据的,使用swagger必须注解
|
19天前
|
SQL 关系型数据库 MySQL
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
|
17天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
15天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制