mysql中json类型字段的基本用法实例-数据库

mysql中json类型字段的基本用法实例目录前言基本环境 JSON类型字段常用操作插入JSON类型数据查询JSON类型数据更新JSON类型数据中的特定字段匹配JSON类型数据中的特定字段结语前言 mysql从5.7.8版本开始原生支持了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);