快捷導(dǎo)航

mysql中json類型字段的基本用法實例

更新時間：2022年08月12日 09:15:10 作者：有夢想的攻城獅

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

前言

mysql從5.7.8版本開始原生支持了JSON類型數(shù)據(jù)，同時可以對JSON類型字段中的特定的值進(jìn)行查詢和更新等操作，通過增加JSON類型的屬性可以大大的提高我們在mysql表中存儲的數(shù)據(jù)的拓展性，無需每次新增字段時都進(jìn)行表結(jié)構(gòu)的調(diào)整，下面我們不深入講解底層的實現(xiàn)原理，我們主要來梳理一下我們在日常工作中使用實踐

基本環(huán)境

mysql版本：8.0.28

springboot版本： 2.2.2

測試表結(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)對應(yīng)的實體類：

@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)換過程通過fastjson來進(jìn)行，需依賴fastjson對應(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ù)

在項目開發(fā)中，表結(jié)構(gòu)中的json字段可以用JSONObject這樣的比較靈活的方式來傳遞，但是這樣的方式有一個比較大的問題就是我們在獲得這個結(jié)構(gòu)后無法很直觀的確定json字段中存儲的數(shù)據(jù)，比較好的一種方式是我們將表結(jié)構(gòu)中的json類型的字段以自定義的類來存儲，這樣我們再存取這個類對應(yīng)的對象的時候，就可以明確的知道數(shù)據(jù)庫中對應(yīng)的json字段到底存儲的是一些什么樣的key，如果需要進(jìn)行調(diào)整的話，我們只需要在該類中新增新的字段即可，完全無需對數(shù)據(jù)庫進(jìn)行任何的調(diào)整。這樣的存儲方式我們再插入和查詢該字段的時候需要指定一個指定的數(shù)據(jù)類型轉(zhuǎn)換的類來對數(shù)據(jù)庫中的JSON格式數(shù)據(jù)和我們在項目中的自定義類進(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)行查詢時，由于也需要進(jìn)行json格式數(shù)據(jù)和自定義類的轉(zhuǎn)換，所以我們需要指定對應(yīng)的json字段和轉(zhuǎn)換的工具類，通過@Result注解來進(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ù)都取出，然后通過修改當(dāng)前對象的值，然后整個json格式字段set進(jìn)去；第二種方式直接通過json格式的特定SQL語法來進(jìn)行指定key的更新；下面的例子里面我們分別根據(jù)這兩種不同的模式進(jìn)行更新操作

//模式一：整體更新整個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同樣可以用于修改的操作函數(shù)還有：JSON_INSERT、 JSON_REPLACE 、 JSON_REMOVE等，下面簡單說一下這幾個函數(shù)的不同

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類型數(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);