mysql中json型別欄位的基本用法範例

2022-08-12 14:05:38

前言

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型別欄位用法內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！