devbox
Cpp五条
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql 之 Json字段类型实践_mysql json字段

作者：Cpp五条 | 2024-04-10 02:22:32

赞

踩

mysql json字段

前言

JSON类型是MySQL5.7.8中新加入的一种数据类型，并在后续版本尤其是MySQL8.0中得到了大幅增强，现在的JSON类型的功能十分强大，合理使用能让我们的开发更加有效。

本文注重实践，理论相关知识，可以通过以下链接去官方网站了解

英文不好的，可以使用谷歌翻译打开网页

在这里插入图片描述

实验环境

springboot 3.7 + MySQL 5.7.12 + Mybatis-plus 3.5.2

表结构

CREATE TABLE `test_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL COMMENT '名称',
  `age` int(11) DEFAULT 0 COMMENT '年龄',
  `hobbies` varchar(200) DEFAULT NULL COMMENT '爱好',
  `friends` json DEFAULT NULL COMMENT '朋友',
  `books` json DEFAULT NULL COMMENT '书籍',
	/* 下面六个字段为常用字段 */
  `description` varchar(200) DEFAULT NULL COMMENT '描述',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除 [0未删除 1已删除]',
  `create_by` int(11) DEFAULT NULL COMMENT '创建人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` int(11) DEFAULT NULL COMMENT '修改人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='JSON字段测试表';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

实体类

package com.apidoc.demo.entiry;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * JSON字段测试表
 * @TableName test_json
 */

/**
 * ·@TableName的autoResultMap属性与@TableField的typeHandler属性一起使用，表示会自动创建resultMap处理查询返回值
 *  但是这个只是针对mybatis-plus提供的方法，
 *  如果要是自定义的方法，需要在mapper文件的方法名上添加返回类型指定具体查看下面mapper自定义方法
 */
@TableName(value ="test_json", autoResultMap = true)
@Data
public class TestJson implements Serializable {
    /**
     * 
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 名称
     */
    @TableField(value = "name")
    private String name;

    /**
     * 年龄
     */
    @TableField(value = "age")
    private Integer age;

    /**
     * 爱好
     * 使用自定义typehandler处理
     */
    @TableField(value = "hobbies", typeHandler = MyTypeHandler.class)
    private List<String> hobbies;

    /**
     * 朋友
     */
    @TableField(value = "friends", typeHandler = JacksonTypeHandler.class)
    private Map<Integer, String> friends;

    /**
     * 书籍
     * 泛型擦除问题
     */
//    @TableField(value = "books", typeHandler = JacksonTypeHandler.class)
    @TableField(value = "books", typeHandler = MyBookTypeHandler.class)
    private List<MyBook> books;
    /**
     * 描述
     */
    @TableField(value = "description")
    private String description;

    /**
     * 是否删除 [0未删除 1已删除]
     */
    @TableField(value = "deleted")
    private Integer deleted;

    /**
     * 创建人
     */
    @TableField(value = "create_by")
    private Integer createBy;

    /**
     * 创建时间
     */
    @TableField(value = "create_time")
    private LocalDateTime createTime;

    /**
     * 修改人
     */
    @TableField(value = "update_by")
    private Integer updateBy;

    /**
     * 修改时间
     */
    @TableField(value = "update_time")
    private LocalDateTime updateTime;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    public static class MyBook implements Serializable{
        private String bookName;
        private BigDecimal bookMoney;
    }

    /**
     * 自定义类型处理器，用于处理查询泛型擦除问题(只针对自定义类型，如果使用java自带类型不会有问题)
     */
    public static class MyBookTypeHandler extends AbstractJsonTypeHandler<List<MyBook>>{

        @SneakyThrows
        @Override
        protected List<MyBook> parse(String json) {
            ObjectMapper mapper = new ObjectMapper();
            return mapper.readValue(json, new TypeReference<List<MyBook>>(){});
        }

        @SneakyThrows
        @Override
        protected String toJson(List<MyBook> obj) {
            ObjectMapper mapper = new ObjectMapper();
            return mapper.writeValueAsString(obj);
        }
    }
}

  • 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
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136

自定义类型处理器

//数据库类型
@MappedJdbcTypes(JdbcType.VARCHAR)
//java数据类型
@MappedTypes({List.class})
public class MyTypeHandler implements TypeHandler<List<String>> {
    /**
     * 保存数据到数据库会调用这个方法
     * @param preparedStatement
     * @param i
     * @param strings
     * @param jdbcType
     * @throws SQLException
     */
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, List<String> strings, JdbcType jdbcType) throws SQLException {
        //这里，我们将List<String> [1,2,3]转化为 VARCHAR类型 "1,2,3"
        String dbData = getData(strings);
        preparedStatement.setString(i, dbData);
    }

    private String getData(List<String> strings) {
        if(strings == null || strings.size() <=0)
            return null;
        StringBuilder res = new StringBuilder();
        for (int i = 0 ;i < strings.size(); i++) {
            if(i == strings.size()-1){
                res.append(strings.get(i));
                break;
            }
            res.append(strings.get(i)).append(",");
        }
        return res.toString();
    }

    /**
     * 从数据库读取数据会调用这个方法
     * @param resultSet
     * @param s
     * @return
     * @throws SQLException
     */
    @Override
    public List<String> getResult(ResultSet resultSet, String s) throws SQLException {
        return Arrays.asList(resultSet.getString(s).split(","));
    }

    @Override
    public List<String> getResult(ResultSet resultSet, int i) throws SQLException {
        return Arrays.asList(resultSet.getString(i).split(","));
    }

    @Override
    public List<String> getResult(CallableStatement callableStatement, int i) throws SQLException {
        return Arrays.asList(callableStatement.getString(i).split(","));
    }
}

  • 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
  • 56

包含自定义方法的mapper类

public interface TestJsonMapper extends BaseMapper<TestJson> {

    /**
     * 自定义的mapper方法需要设置返回值类型，这样会使用typehandler将数据解析到对象中
     * 设置了ResultMap为`mybatis-plus_TestJson`后就可以拿到正确的值.
     * 命名规则就是：mybatis-plus_{实体类名}
     * @param id
     * @return
     */
    @ResultMap("mybatis-plus_TestJson")
    @Select("select * from test_json where id = #{id}")
    TestJson getInfoById(int id);
}

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

Mapper对应XML文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.apidoc.demo.mapper.TestJsonMapper">

<!--    <resultMap id="BaseResultMap" type="com.apidoc.demo.entiry.TestJson">-->
<!--            <id property="id" column="id" jdbcType="INTEGER"/>-->
<!--            <result property="name" column="name" jdbcType="VARCHAR"/>-->
<!--            <result property="age" column="age" jdbcType="INTEGER"/>-->
<!--            <result property="hobbies" column="hobbies" jdbcType="VARCHAR" javaType="java.util.List" typeHandler="com.apidoc.demo.entiry.MyTypeHandler"/>-->
<!--            <result property="friends" column="friends" jdbcType="JAVA_OBJECT" javaType="java.util.Map" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>-->
<!--            <result property="books" column="books" jdbcType="JAVA_OBJECT" javaType="java.util.List" typeHandler="com.apidoc.demo.entiry.TestJson$MyBookTypeHandler"/>-->
<!--            <result property="description" column="description" jdbcType="VARCHAR"/>-->
<!--            <result property="deleted" column="deleted" jdbcType="TINYINT"/>-->
<!--            <result property="createBy" column="create_by" jdbcType="INTEGER"/>-->
<!--            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>-->
<!--            <result property="updateBy" column="update_by" jdbcType="INTEGER"/>-->
<!--            <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>-->
<!--    </resultMap>-->

    <sql id="Base_Column_List">
        id,name,age,
        hobbies,friends,books,description,
        deleted,create_by,create_time,
        update_by,update_time
    </sql>
</mapper>

  • 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

上述代码中使用的 jdbcType="JAVA_OBJECT" 中，jdbcType的取值范围来源于org.apache.ibatis.type.JdbcType 枚举。
注意：如果你在xml文件中使用了type="com.apidoc.demo.entiry.TestJson"，则会使TestJson实体类中@TableName(autoResultMap = true) 失效，你需要将实体类中定义的同步到xml配置中才行。

测试方法类


@SpringBootTest
class DemoApplicationTests {

    @Test
    void contextLoads() {
    }

    @Autowired
    private TestJsonService testJsonService;

    @Test
    void testHandler(){
        TestJson testJson = new TestJson();
        testJson.setAge(10);
        testJson.setName("111");
        testJson.setCreateBy(1);
        testJson.setCreateTime(LocalDateTime.now());
        List<String> hobbies = new ArrayList<String>(){{
            add("eat");
            add("drink");
            add("play");
        }};
        testJson.setHobbies(hobbies);
        Map<Integer, String> friends = new HashMap<Integer, String>(){{
           put(1, "ZhangSan");
           put(2, "LiSi");
           put(3, "WangWu");
        }};
        testJson.setFriends(friends);

        List<TestJson.MyBook> books = new ArrayList<TestJson.MyBook>(){{
            add(new TestJson.MyBook("My Child", new BigDecimal("35.30")));
            add(new TestJson.MyBook("My Life", new BigDecimal("55.20")));
            add(new TestJson.MyBook("My College", new BigDecimal("70.00")));
        }};
        testJson.setBooks(books);

        testJsonService.save(testJson);

        TestJson byId = testJsonService.getById(testJson.getId());
        System.out.println("call MP method");
        System.out.println(byId);

        TestJson infoById = testJsonService.getInfoById(testJson.getId());
        System.out.println("call self-define method");
        System.out.println(infoById);
    }
}

  • 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

实验结果

1.使用自定义类型处理器MyTypeHandler将List转为以逗号分隔的字符串存入数据库，读取时为写入的逆过程

代码：
在这里插入图片描述
数据库：
在这里插入图片描述
再次查询：
在这里插入图片描述

2.使用Jackson自带的类型转换器将Map对象保存为json类型数据

代码：
在这里插入图片描述
数据库：
在这里插入图片描述
再次查询：
在这里插入图片描述

3.使用自定义类型为泛型字段保存为json字段

代码：
在这里插入图片描述
数据库：
在这里插入图片描述
使用jackson自带类型处理器JacksonTypeHandler查询：
在这里插入图片描述
上面可以看到，查询出的结果已经将数据类型改变了。
解决方法是使用自定义的类型处理器MyBookTypeHandler
在这里插入图片描述

同时可以看到，引用内部类的方法是类名$内部类名，这个在xml中常用。

4.调用自定义mapper方法

在这里插入图片描述
发现调用mybatis-plus提供的方法，有数据；调用自己写的方法，缺失部分数据。
解决方法是在自定义方法上添加 @ResultMap("mybatis-plus_TestJson") ,
ResultMap的命名规则为 mybatis-plus_{实体类名}。

总结

  • JSON类型的功能十分强大，可以通过MySQL提供的JSON相关的方法直接操作JSON字段中的某个属性值，也可以针对JSON类型字段的数据进行各种操作。

  • 相对于使用字符串类型保存JSON字符串的方式而言，使用JSON类型的另外一个好处是可以在程序上定义JSON格式，提供更友好而规范的API文档。

  • 另外如果不想使用JSON类型，直接用Varchar类型也是可以的！如果用JSON类型可以根据JSON对象的字段进行条件查询！

扩展

1.json相关操作

说明：以下操作基于mysql 8.x

准备数据

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, "order": 1, "value": "34252"},{"key": 2, "order": 2, "value": "23423"}]' ),
	/*这条是数组*/
	( 2, '[{"key": 4, "order": 4, "value": "234"},{"key": 5, "order": 5, "value": "234324523"}]' ),
	/*这条是对象*/
	( 3, '{"key": 3, "order": 3, "value": "43242"}' ),
	/*这条是对象*/
	( 4, '{"key": 6, "order": 6, "value": "5423"}' );

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

查询指定字段值

/* 基础查询 */
SELECT
	content -> '$.key' AS 'key',
	JSON_EXTRACT(content, '$.key') AS 'key2',
	content -> '$.value' AS 'value',
	JSON_EXTRACT(content, '$.value') AS 'value2',
	content ->> '$.value' AS 'value3',
	JSON_UNQUOTE(JSON_EXTRACT(content, '$.value')) AS 'value4'
FROM
	json_demo 
WHERE
	id > 2;


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

用于条件查询

SELECT
	id,
	content -> '$.key' AS 'key',
	content ->> '$.value' AS 'value3'
FROM
	json_demo 
WHERE
	id > 2
	AND content -> '$.key' > 1
	AND content -> '$.value' like '%2%';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

修改指定字段值

/* 修改 */
UPDATE json_demo 
SET content = JSON_REPLACE(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* JSON_SET也可以 */
UPDATE json_demo 
SET content = JSON_SET(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* 查询修改结果 */
SELECT id,content,content -> '$.key' AS 'key',content ->> '$.value' AS 'value3'
FROM json_demo WHERE id = 3;
/* 重新赋值 */
UPDATE json_demo SET 
content = JSON_REPLACE(content,'$.key',3,'$.value','43242') WHERE id = 3;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

追加元素

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.key', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.temp', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_SET(content, '$.temp2', 432)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

更多点击这里查看 MySQL JSON类型字段使用技巧

2.json字段添加索引

MySQL为JSON字段创建索引

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/396103
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号