mysql中json类型字段的基本用法实例

mysql中json类型字段的基本用法实例

目录

前言

基本环境

JSON类型字段常用操作

插入JSON类型数据

查询JSON类型数据

更新JSON类型数据中的特定字段

匹配JSON类型数据中的特定字段

结语

前言

mysql从5.7.8版本开始原生支持了JSON类型数据,同时可以对JSON类型字段中的特定的值进行查询和更新等操作,通过增加JSON类型的属性可以大大的提高我们在mysql表中存储的数据的拓展性,无需每次新增字段时都进行表结构的调整,下面我们不深入讲解底层的实现原理,我们主要来梳理一下我们在日常工作中使用实践

基本环境

mysql版本:8.0.28

springboot版本: 2.2.2

测试表结构:

CREATE TABLE `t_json` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `json_obj` json DEFAULT NULL, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表结构对应的实体类:

@Data public class JsonTest { private Integer id; private String name; private JsonObj jsonObj; }

JsonObj类

@Data @Builder @NoArgsConstructor @AllArgsConstructor public class JsonObj { private String data; private int age; }

自定义格式转换类:转换过程通过fastjson来进行,需依赖fastjson对应的pom文件

import com.alibaba.fastjson.JSON; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedTypes; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @MappedTypes(String.class) @Slf4j public class JsonTypeHandler<T extends Object> extends BaseTypeHandler<T> { private Class<T> clazz; public JsonTypeHandler(Class<T> clazz) { if (clazz == null) { throw new IllegalArgumentException("Type argument cannot be null"); } this.clazz = clazz; } @Override public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { ps.setString(i, this.toJson(parameter)); } @Override public T getNullableResult(ResultSet rs, String columnName) throws SQLException { return this.toObject(rs.getString(columnName), clazz); } @Override public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return this.toObject(rs.getString(columnIndex), clazz); } @Override public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return this.toObject(cs.getString(columnIndex), clazz); } private String toJson(T object) { return JSON.toJSONString(object); } private T toObject(String content, Class<?> clazz) { if (content != null && !content.isEmpty()) { return (T) JSON.parseObject(content, clazz); } else { return null; } } } JSON类型字段常用操作 插入JSON类型数据

在项目开发中,表结构中的json字段可以用JSONObject这样的比较灵活的方式来传递,但是这样的方式有一个比较大的问题就是我们在获得这个结构后无法很直观的确定json字段中存储的数据,比较好的一种方式是我们将表结构中的json类型的字段以自定义的类来存储,这样我们再存取这个类对应的对象的时候,就可以明确的知道数据库中对应的json字段到底存储的是一些什么样的key,如果需要进行调整的话,我们只需要在该类中新增新的字段即可,完全无需对数据库进行任何的调整。这样的存储方式我们再插入和查询该字段的时候需要指定一个指定的数据类型转换的类来对数据库中的JSON格式数据和我们在项目中的自定义类进行转换的类,具体如下图所示:

@Mapper public interface JsonMapper { @Insert({ "insert into t_json set name= #{name}, json_obj = #{jsonObj ,jdbcType=OTHER, typeHandler=cn.example.eureka.service.one.config.JsonTypeHandler}" }) int insert(JsonTest json); } 查询JSON类型数据

在进行查询时,由于也需要进行json格式数据和自定义类的转换,所以我们需要指定对应的json字段和转换的工具类,通过@Result注解来进行指定

@Mapper public interface JsonMapper { @Select({"<script>", "select * from t_json where id = #{id}", "</script>"}) @Results( @Result(column = "json_obj", property = "jsonObj", typeHandler = JsonTypeHandler.class) ) JsonTest getById(Integer id); } 更新JSON类型数据中的特定字段

进行指定字段的更新的话,有两种方式可以采用,一种方式是先将该json格式字段中的所有数据都取出,然后通过修改当前对象的值,然后整个json格式字段set进去;第二种方式直接通过json格式的特定SQL语法来进行指定key的更新;下面的例子里面我们分别根据这两种不同的模式进行更新操作

//模式一:整体更新整个json字段 @Update({ "update t_json set json_obj = #{jsonObj ,jdbcType=OTHER, typeHandler=cn.example.eureka.service.one.config.JsonTypeHandler} where id = #{id}" }) int update(JsonTest jsonTest); //模式二:只更新json字段中的特定key @Update({ "update t_json set json_obj = JSON_SET(json_obj, '$.data', #{data}) where id = #{id}" }) int updateData(@Param("id") Integer id, @Param("data") String data);

说明

和上面的JSON_SET同样可以用于修改的操作函数还有:JSON_INSERT、 JSON_REPLACE 、 JSON_REMOVE等,下面简单说一下这几个函数的不同

JSON_INSERT : json字段中的key不存在才会修改

JSON_REPLACE : json字段中的key存在才会修改

JSON_SET : json字段中的可以不管是否存在都会修改

JSON_REMOVE : 移除json字段中的指定key

如下所示JSON_REMOVE的用法:

@Update({ "update t_json set json_obj = JSON_REMOVE(json_obj, '$.age') where id = #{id}" }) int removeAge(@Param("id") Integer id); 匹配JSON类型数据中的特定字段 //模式一 @Select({ "select * from t_json where json_obj -> '$.age' = #{age}" }) @Results( @Result(column = "json_obj", property = "jsonObj", typeHandler = JsonTypeHandler.class) ) List<JsonTest> getByAge(int age); //模式二 @Select({ "select * from t_json where JSON_EXTRACT(json_obj , '$.data') = #{data}" }) @Results( @Result(column = "json_obj", property = "jsonObj", typeHandler = JsonTypeHandler.class) ) List<JsonTest> getByData(String data); 结语

到此这篇关于mysql中json类型字段的基本用法的文章就介绍到这了,更多相关mysql json类型字段用法内容请搜索易知道(ezd.cc)以前的文章或继续浏览下面的相关文章希望大家以后多多支持易知道(ezd.cc)!

推荐阅读

    mac怎么安装mysql| macbook怎么下载mysql

    mac怎么安装mysql| macbook怎么下载mysql,标签,学习python 不得不学习的就是数据库,那么在开始学习数据库之前,那么今天我们先说下怎么安装m

    MySQL更新更新页面1/4。

    MySQL更新更新页面1/4。,,插入和替换 插入和替换语句的功能是将新数据插入表中,这两个语句的语法相似,它们之间的主要区别是如何处理重复的

    SQLite使用JSON扩展

    SQLite使用JSON扩展,插件,加载,一、介绍 SQLite3.9.0之后的版本,添加了JSON扩展。在表中可以保存JSON类型。实际上SQLite将JSON类型的

    postgresql中对jsonb的查询及转换

    postgresql中对jsonb的查询及转换,数据,字段,表数据:需要将strata排除,并且过滤info字段中为{}的数据,将jsonb转换成text,替换“,{,}见SQL:sele

    node express返回json数据给前端

    node express返回json数据给前端,上传,格式,请注意,使用res.json()的格式1.前面不能添加 res.writeHead(200, {‘content-type‘: ‘text/

    mysql数据库启动失败

    mysql数据库启动失败,报错,非正常,1、原因 公司服务器故障,非正常停机导致数据库启动失败。 报错信息 [ [email protected] dmp]# /etc/ini