mysql中json類型字段的基本用法實(shí)例

更新時(shí)間：2022年08月12日 09:15:10 作者：有夢(mèng)想的攻城獅

大家應(yīng)該都知道m(xù)ysql中的字段可以使用json的格式來(lái)進(jìn)行存儲(chǔ),下面這篇文章主要給大家介紹了關(guān)于mysql中json類型字段的基本用法,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

mysql從5.7.8版本開始原生支持了JSON類型數(shù)據(jù)，同時(shí)可以對(duì)JSON類型字段中的特定的值進(jìn)行查詢和更新等操作，通過(guò)增加JSON類型的屬性可以大大的提高我們?cè)趍ysql表中存儲(chǔ)的數(shù)據(jù)的拓展性，無(wú)需每次新增字段時(shí)都進(jìn)行表結(jié)構(gòu)的調(diào)整，下面我們不深入講解底層的實(shí)現(xiàn)原理，我們主要來(lái)梳理一下我們?cè)谌粘９ぷ髦惺褂脤?shí)踐

基本環(huán)境

mysql版本：8.0.28

springboot版本： 2.2.2

測(cè)試表結(jié)構(gòu)：

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;

表結(jié)構(gòu)對(duì)應(yīng)的實(shí)體類：

@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;
}

自定義格式轉(zhuǎn)換類：轉(zhuǎn)換過(guò)程通過(guò)fastjson來(lái)進(jìn)行，需依賴fastjson對(duì)應(yīng)的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類型數(shù)據(jù)

在項(xiàng)目開發(fā)中，表結(jié)構(gòu)中的json字段可以用JSONObject這樣的比較靈活的方式來(lái)傳遞，但是這樣的方式有一個(gè)比較大的問(wèn)題就是我們?cè)讷@得這個(gè)結(jié)構(gòu)后無(wú)法很直觀的確定json字段中存儲(chǔ)的數(shù)據(jù)，比較好的一種方式是我們將表結(jié)構(gòu)中的json類型的字段以自定義的類來(lái)存儲(chǔ)，這樣我們?cè)俅嫒∵@個(gè)類對(duì)應(yīng)的對(duì)象的時(shí)候，就可以明確的知道數(shù)據(jù)庫(kù)中對(duì)應(yīng)的json字段到底存儲(chǔ)的是一些什么樣的key，如果需要進(jìn)行調(diào)整的話，我們只需要在該類中新增新的字段即可，完全無(wú)需對(duì)數(shù)據(jù)庫(kù)進(jìn)行任何的調(diào)整。這樣的存儲(chǔ)方式我們?cè)俨迦牒筒樵冊(cè)撟侄蔚臅r(shí)候需要指定一個(gè)指定的數(shù)據(jù)類型轉(zhuǎn)換的類來(lái)對(duì)數(shù)據(jù)庫(kù)中的JSON格式數(shù)據(jù)和我們?cè)陧?xiàng)目中的自定義類進(jìn)行轉(zhuǎn)換的類，具體如下圖所示：

@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類型數(shù)據(jù)

在進(jìn)行查詢時(shí)，由于也需要進(jìn)行json格式數(shù)據(jù)和自定義類的轉(zhuǎn)換，所以我們需要指定對(duì)應(yīng)的json字段和轉(zhuǎn)換的工具類，通過(guò)@Result注解來(lái)進(jìn)行指定

@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類型數(shù)據(jù)中的特定字段

進(jìn)行指定字段的更新的話，有兩種方式可以采用，一種方式是先將該json格式字段中的所有數(shù)據(jù)都取出，然后通過(guò)修改當(dāng)前對(duì)象的值，然后整個(gè)json格式字段set進(jìn)去；第二種方式直接通過(guò)json格式的特定SQL語(yǔ)法來(lái)進(jìn)行指定key的更新；下面的例子里面我們分別根據(jù)這兩種不同的模式進(jìn)行更新操作

//模式一：整體更新整個(gè)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);

說(shuō)明

和上面的JSON_SET同樣可以用于修改的操作函數(shù)還有：JSON_INSERT、 JSON_REPLACE 、 JSON_REMOVE等，下面簡(jiǎn)單說(shuō)一下這幾個(gè)函數(shù)的不同

JSON_INSERT : json字段中的key不存在才會(huì)修改
JSON_REPLACE : json字段中的key存在才會(huì)修改
JSON_SET : json字段中的可以不管是否存在都會(huì)修改
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類型數(shù)據(jù)中的特定字段

//模式一
@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);