2-MySQL的JSON数据类型

MySQL 的 JSON 格式字段用法

1、概述

MySQL 5.7.8 新增 JSON 数据类型，用于定义 JSON 格式的数据。

在此之前，表中要存储 JSON 数据都是定义一个 varchar 类型字段，客户端序列化和反序列化处理。但是这种方法不具备强约束性，只要是字符串存什么都行。

而新的 JSON 类型会校验数据格式，只能存储 JSONObject 类型和 JSONArray 类型。

JSONObject:

?
1
2
3
{
  "name": "aaa"
}

 JSONArray：

?
1
[<br>  {"name": "aaa"},{"name":"bbb"}<br>]

键只能为字符串
值类型支持 null, string, boolean, number, object, array

2、定义

创建表时指定字段类型为 JSON，JSON 类型无需指定长度，且默认值只能为 null。
JSON 字段类型不用显示指定是对象还是数组结构，根据实际存储数据自动推断

?
1
2
3
4
5
6
7
CREATE TABLE `t_json_tbl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `json_obj` json DEFAULT NULL COMMENT 'json 对象字段',
  `json_arr` json DEFAULT NULL COMMENT 'json 数组字段',
  `json_str` varchar(255) DEFAULT NULL COMMENT 'json 格式字符串字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3、插入数据

方式 1：
以普通字符串形式插入，需要遵循 json 格式

?
1
2
3
insert into t_json_tbl(json_obj, json_arr, json_str)
  values('{"name":"tom", "age":21, "tags":["a", "b"]}', '["aa", "bb", "cc"]', '{"name":"jj"}');
-- id=1

方式 2：
使用 JSON 内置创建函数

创建 JSON 对象：JSON_OBJECT([key, val[, key, val] ...])
创建 JSON 数组：JSON_ARRAY([val[, val] ...])
函数文档：json-creation-functions

函数可以嵌套使用

?
1
2
3
insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', JSON_ARRAY('c', 'd')), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=2

 （也可以通过 JSON 函数操作普通字符串类型数据）

注意不要这么使用:

?
1
2
3
4
-- JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]') 第二个字段的值以普通字符串插入，不是数组格式
insert into t_json_tbl(json_obj, json_arr, json_str)
  values(JSON_OBJECT('name', 'jerry', 'tags', '["c", "d"]'), JSON_ARRAY('xx', 'yy', 'cc'), JSON_OBJECT('name', 'jack'));
-- id=3

 同名的字段只会保留第一个

4、查询数据

 按照普通字符串去查询整个字段数据：

?
1
select json_obj, json_arr from t_json_tbl;

 

查询 JSON 中字段的数据

查询 JSON 字段数据，需要 column -> 'path' 形式访问指定字段的具体数据。
注意这个 'path' 外层是有一个单引号的

其中 column 表示要查询的数据字段列名；
path 为 JSON 数据的访问路径，path格式为 $.path 或 $[idx]。

  • $.path 用于 JSONObject 类型数据；
  • $[idx] 用于 JSONArray 类型数据；
  • $ 代表整个 JSON 数据的 root 节点；
  • path 为访问字段 key，如果字段名包含空格，则需要用双引号包住，如 $."nick name"；
  • [idx] 是数组的索引。

例：

?
1
2
3
4
5
{
  "name":"tom",
  "age":21,
  "tags":["a", "b"]
}
  • $.name 值为 "tom"
  • $.tags[0] 值为 "a"

查询示例：

?
1
select json_obj->'$.name' `name`, json_obj->'$.tags[0]' `tags0`, json_arr->'$[0]' xx from t_json_tbl;

 

 -> 查询到的字段字符串类型还会有个双引号，还需要做一层处理
可以使用 ->> 去除，且转义符也会去除

?
1
select json_obj->>'$.name' `name`, json_obj->>'$.tags[0]' `tags0`, json_arr->>'$[0]' xx from t_json_tbl;

 

可以使用内置函数进行查询

  • JSON_EXTRACT(column, path) 等价于 column->path
  • JSON_UNQUOTE(JSON_EXTRACT(column, path)) 等价于 column->>path

其他内置查询函数：json-search-functions

条件查询

?
1
select * from t_json_tbl where json_obj->'$.name' = 'Merry';

模糊查询

JSON 字段的模糊搜索仅支持 %str% 格式，也因此其模糊搜索时索引是失效的

?
1
select * from t_json_tbl where json_obj->'$.name' like '%tom%';

5、更新操作


使用内置函数更新 JSON 字段：json-modification-functions

更新字段

  •     JSON_SET(json_doc, path, val[, path, val] ...) 更新或插入
  •     JSON_REPLACE(json_doc, path, val[, path, val] ...) 只更新
?
1
2
3
4
5
update t_json_tbl
set
json_obj = JSON_SET(json_obj, '$.name', 'Merry'),
json_arr = JSON_SET(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 1;

新增字段

  •     JSON_INSERT(json_doc, path, val[, path, val] ...) 插入新字段，不会改变已经存在的
?
1
2
3
4
5
update t_json_tbl
set
json_obj = JSON_INSERT(json_obj, '$.name', 'Merry'),
json_arr = JSON_INSERT(json_arr, '$[0]', 'aa2222', '$[2]', 'gggg', '$[7]', 'fdsfd')
where id = 2;

 

删除字段

  •     JSON_REMOVE(json_doc, path[, path] ...) 删除字段
?
1
update t_json_tbl <br>set <br>json_obj = JSON_REMOVE(json_obj, '$.tags'),<br>json_arr = JSON_REMOVE(json_arr, '$[0]', '$[7]')<br>where id = 3;

其他操作函数：json-modification-functions

6、索引使用

JSON 字段不支持原生索引，需要基于 JSON 字段创建一个生成列，然后给这个生成列创建索引。插入数据时会自动填充生成列。
使用生成列作为条件，可以执行 like 模糊搜索，索引是生效的

新建一个表

?
1
2
3
4
5
6
7
CREATE TABLE `t_json_tbl2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `json_obj` json DEFAULT NULL COMMENT 'json 数据',
  `gen_col` int(11) GENERATED ALWAYS AS (json_extract(`json_obj`,'$.num')) VIRTUAL COMMENT '生成列',
  PRIMARY KEY (`id`),
  KEY `idx_gen_col` (`gen_col`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 写个存储过程，插入大量数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop PROCEDURE if exists batchInsert;
DELIMITER $$
create PROCEDURE batchInsert(n int)
begin
declare i int default 0;
SET autocommit=0;
while i <= n
do
set i = i+1;
insert into t_json_tbl2(json_obj) values(JSON_OBJECT('name', concat('lisi-', i), 'num', i));
end while;
SET autocommit=1;
end $$
DELIMITER ;<br>-- 调用<br>call batchInsert(5000000);

查询

?
1
2
3
4
-- 可以这么写
select * from t_json_tbl2 where gen_col = 555555;
-- 或者这么写
select * from t_json_tbl2 where json_obj->'$.num' = 555555;

执行计划（数值型的使用->可以生效，但是字符串型的需要用生成列作为索引条件才能生效）

?
1
explain select * from t_json_tbl2 where gen_col = 555555;

 

?
1
explain select * from t_json_tbl2 where json_obj->'$.num' = 555555;

 

7、Java 操作

使用 Mybatis-plus 操作，需要配置类型处理器

实体类

?
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
@Data
@TableName(value = "t_json_tbl", autoResultMap=true)
public class JsonTbl implements Serializable {
    private static final long serialVersionUID = 1L;
  
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
  
    /**
     * json 对象字段
     * 给 mybatis-plus 配置类型处理器
     */
    @TableField(typeHandler = JacksonTypeHandler.class)
    private JsonObj jsonObj;
  
    /**
     * json 数组字段
     */
    private String jsonArr;
  
    /**
     * json 格式字符串字段
     */
    private String jsonStr;
  
}
@Data
public class JsonObj {
    private String name;
    private Integer age;
}

 mapper

?
1
2
3
4
5
6
public interface JsonTblMapper extends BaseMapper<JsonTbl> {
  
    JsonTbl selectBy(Long id);
    JsonTbl selectLike(String name);
  
}

 xml 配置文件

?
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
<?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="top.originyy.case11.mybatis.mapper.JsonTblMapper">
  
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="top.originyy.case11.mybatis.entity.JsonTbl">
        <id column="id" property="id"/>
        <!--    配置类型处理器    -->
        <result column="json_obj" property="jsonObj"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
        <result column="json_arr" property="jsonArr"/>
        <result column="json_str" property="jsonStr"/>
    </resultMap>
  
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, json_obj, json_arr, json_str
    </sql>
     
    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectBy" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `id`=#{id}
    </select>
  
    <!--  自定义 sql 使用 resultMap 返回数据 -->
    <select id="selectLike" resultMap="BaseResultMap">
        select *
        from t_json_tbl
        where
        `json_obj`->'$.name'=#{name}
    </select>
  
</mapper>

8、官方文档

MySQL 5.7 的文档：
https://dev.mysql.com/doc/refman/5.7/en/json.html
JSON 操作函数目录：
json-function-reference
索引创建：
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index

posted @   幻想被孤立  阅读(209)  评论(0)  编辑  收藏  举报
相关博文：
· 1-安装MySQL数据库
· 1_Linux文件管理方式及常用命令
· MySQL 的 JSON 格式字段用法
· MySQL 支持JSON类型字段
· 一文说透 MySQL JSON 数据类型（收藏）
阅读排行：
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
点击右上角即可分享
微信分享提示