mysql中json类型字段的基本用法实例

目录
  • 前言
  • 基本环境
  • JSON类型字段常用操作
    • 插入JSON类型数据
    • 查询JSON类型数据
    • 更新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);

结语

到此这篇关于mysql中json类型字段的基本用法的文章就介绍到这了,更多相关mysql json类型字段用法内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql5.7 新增的json字段类型用法实例分析

    本文实例讲述了mysql5.7 新增的json字段类型用法.分享给大家供大家参考,具体如下: 一.我们先创建一个表,准备点数据 CREATE TABLE `json_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `json` json DEFAULT NULL COMMENT 'json数据', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8m

  • MySQL5.7 JSON类型使用详解

    JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单:名称/键值.之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 我们先看看MySQL老版本的JSON存取. 示例表结构: CREATE TABLE json_test( id INT, p

  • MySQL操作之JSON数据类型操作详解

    上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容. 概述 mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点.但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的. 创建一个JSON字段的表 首先先创建一个表,这个表包含一个json格式的字段: CREATE TABLE table_name ( id INT NOT NULL

  • mysql查询字段类型为json时的两种查询方式

    表结构如下: id varchar(32) info json 数据: id = 1 info = {"age": "18","disname":"小明"} -------------------------------------------- 现在我需要获取info中disanme的值,查询方法有: 1. select t.id,JSON_EXTRACT(t.info,'$.disname') as disname fro

  • MySQL对JSON类型字段数据进行提取和查询的实现

    目录 前言 1. 问题现象 2. 解决方案 3. JSON数据查询 3.1 一般基础查询操作 3.2 一般函数查询操作 4. JSON数据新增更新删除 前言 昨天上线后通过系统报警发现了一个bug,于是紧急进行了回滚操作,但是期间有用户下单,数据产生了影响,因此需要排查影响了哪些订单,并对数据进行修复. 1. 问题现象 由于bug导致了订单表的customer_extra_info字段的hasFreightInsurance误更新成了“是”,因此需要查询回滚前一共有多少被误更新为“是”的订单,如

  • MySQL中JSON字段数据类型详解

    目录 前言 创建JSON值 搜索JSON类型数据 在 JSON 和非 JSON 值之间转换 JSON 值的汇总 总结 前言 JSON 类型是从 MySQL 5.7 版本开始支持的功能,MySQL 支持由RFC 7159定义的本机JSON数据类型,该类型可有效访问 JSON(JavaScript 对象 table 示法)文档中的数据.与将 JSON 格式的字符串存储在字符串列中相比,JSON数据类型具有以下优点: 自动验证存储在JSON列中的 JSON 文档.无效的文档会产生错误. 优化的存储格式

  • mysql中json类型字段的基本用法实例

    目录 前言 基本环境 JSON类型字段常用操作 插入JSON类型数据 查询JSON类型数据 更新JSON类型数据中的特定字段 匹配JSON类型数据中的特定字段 结语 前言 mysql从5.7.8版本开始原生支持了JSON类型数据,同时可以对JSON类型字段中的特定的值进行查询和更新等操作,通过增加JSON类型的属性可以大大的提高我们在mysql表中存储的数据的拓展性,无需每次新增字段时都进行表结构的调整,下面我们不深入讲解底层的实现原理,我们主要来梳理一下我们在日常工作中使用实践 基本环境 my

  • MySQL之JSON类型字段的使用技巧分享

    目录 准备工作 JSON对象基础操作 JSON数组操作 更多操作 JSON字段在JAVA中的实践 测试环境: MySQL8.0.19 准备工作 CREATE TABLE json_demo ( `id` INT ( 11 ) NOT NULL PRIMARY KEY, `content` json NOT NULL ); INSERT INTO json_demo ( id, content ) VALUES /*这条是数组*/ ( 1, '[{"key": 1, "orde

  • Mysql json类型字段Java+Mybatis数据字典功能的实践方式

    目录 前言 应用场景介绍 数据字典案例实践 表结构 实体类 关系映射 泛型擦除问题解决(7.21新增) 效果展示 总结 前言 JSON类型是MySQL5.7.8中新加入的一种数据类型,并在后续版本尤其是MySQL8.0中得到了大幅增强,现在的JSON类型的功能十分强大,合理使用能让我们的开发更加有效! 但本文不准备花篇幅来介绍MySQL的JSON类型字段的相关API,因为官方文档里面写得已经十分详细了,大家如果对MySQL的JSON类型还不怎么了解的话可以看看官方文档: MySQL5.7 JSO

  • 解决python写入mysql中datetime类型遇到的问题

    刚开始使用python,还不太熟练,遇到一个datetime数据类型的问题: 在mysql数据库中,有一个datetime类型的字段用于存储记录的日期时间值.python程序中有对应的一个datetime变量dt. 现在需要往mysql数据库中添加记录,每次添加时,将datetime型变量dt写入mysql数据库tablename表中exTime字段里. 问题,如何写入?调试时,总是无法写入. 运行环境:windows10 python 3.6 mysql5.6.38 运行结果提示: Proce

  • MySQL中json字段的操作方法

    MySQL5.7.8中引入了json字段,这种类型的字段使用的频率比较低,但是在实际操作中,有些业务仍然在用,我们以此为例,介绍下json字段的操作方法: 还是从例子看起: mysql> create table test1(id int,info json); Query OK, 0 rows affected (0.02 sec) mysql> insert into test1 values (1,'{"name":"yeyz","age

  • Django中模型Model添加JSON类型字段的方法

    本文实例讲述了Django中模型Model添加JSON类型字段的方法.分享给大家供大家参考.具体如下: Django里面让Model用于JSON字段,添加一个JSONField自动类型如下: class JSONField(models.TextField): __metaclass__ = models.SubfieldBase description = "Json" def to_python(self, value): v = models.TextField.to_pytho

  • 详解MySQL中数据类型和字段类型

    目录 1. mysql的数据类型 (1)数值型 (2)字符(串)型 (3)日期和时间型 (4)null值 2. mysql的列(字段)类型 2.1数值列类型 2.2字符串列类型 2.3日期时间列类型 1. mysql的数据类型 在mysql中有如下几种数据类型: (1)数值型 数值是诸如32 或153.4 这样的值.mysql 支持科学表示法,科学表示法由整数或浮点数后跟“e”或“e”.一个符号(“+”或“-”)和一个整数指数来表示.1.24e+12 和23.47e-1 都是合法的科学表示法表示

  • Postgresql数据库中的json类型字段使用示例详解

    目录 1. Json概述 2. Postgresql数据库中使用Json类型字段 2.1. 创建表定义字段信息 2.2. 增加 2.3. 查询键值 2.3.1. 查询键 2.3.2. 查询值 2.3.3. where查询条件使用json键值作为条件 PostgreSQL 最重要的文档性数据类型就是JSON了,与 MongoDB 的BSON相比较,PostgreSQL 或许更加强大,因为它能与原有的关系性范式兼容,给数据库存储与维护带来了更多的可行性和便利性. 1. Json概述 JSON 代表

随机推荐