mysql中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 extends BaseTypeHandler {
    private Class clazz;

    public JsonTypeHandler(Class 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;
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

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);
}
1
2
3
4
5
6
7
8

查询JSON类型数据

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

@Mapper
public interface JsonMapper {


    @Select({""})
    @Results(
            @Result(column = "json_obj", property = "jsonObj", typeHandler = JsonTypeHandler.class)
    )
    JsonTest getById(Integer id);

}
1
2
3
4
5
6
7
8
9
10
11
12
13

更新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);
1
2
3
4
5
6
7
8
9
10
11
12

说明

和上面的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);
1
2
3
4

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

//模式一
@Select({
            "select * from t_json where json_obj -> '$.age' = #{age}"
    })
    @Results(
            @Result(column = "json_obj", property = "jsonObj", typeHandler = JsonTypeHandler.class)
    )
    List 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 getByData(String data);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

结语

今天先写这么多，接下来再不断补充

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦