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

修己xj

发布于 2023-08-25 11:31:55

2.3K0

发布于 2023-08-25 11:31:55

文章被收录于专栏：修己xj

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

使用

创建包含JSON字段的表在MySQL中，我们可以使用JSON数据类型来定义表的字段。例如，我们可以创建一个名为jsontest的表，其中包含一个名为details的JSON字段，用于存储工单的信息。下面是创建这样一张表的示例SQL语句：

CREATE TABLE jsontest (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    details JSON
);

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

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}');

查询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

结果：

过滤和排序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;

结果：

更新JSON数据 MySQL提供了函数来更新JSON字段中的数据。例如，我们可以使用JSON_SET()函数工单名称：

UPDATE jsontest 
SET details = JSON_SET( details, '$.title', 'xj-update1' ) 
WHERE
 id = 1;

删除JSON数据类似于更新操作，我们也可以使用函数来删除JSON字段中的数据。例如，我们可以使用JSON_REMOVE()函数删除工单的调换类型：

UPDATE jsontest 
SET details = JSON_REMOVE( details, '$.jumpFlag' ) 
WHERE
 id = 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;

结果：

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功能都将为您带来更多可能性和创新空间。

参考文献：