MySQL 的 JSON 格式字段用法

1、概述

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

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

JSONObject:

{
  "name": "aaa"
}

JSONArray:

[
  {"name":"aaa"}, {}
]

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

2、定义

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

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 格式

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

函数可以嵌套使用

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 函数操作普通字符串类型数据）

注意不要这么使用:

-- 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、查询操作

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

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] 是数组的索引。

例：

{
  "name":"tom", 
  "age":21, 
  "tags":["a", "b"]
}

• $.name 值为 "tom"
• $.tags[0] 值为 "a"

查询示例：

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

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

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

条件查询

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

模糊查询

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

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] ...) 只更新

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] ...) 插入新字段，不会改变已经存在的

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] ...) 删除字段

update t_json_tbl 
set 
json_obj = JSON_REMOVE(json_obj, '$.tags'),
json_arr = JSON_REMOVE(json_arr, '$[0]', '$[7]')
where id = 3;
 

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

6、索引使用

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

新建一个表

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;

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

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 ;
 
-- 调用
call batchInsert(5000000);

查询

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

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

explain select * from t_json_tbl2 where gen_col = 555555;

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

7、Java 操作

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

实体类

@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

public interface JsonTblMapper extends BaseMapper<JsonTbl> {
 
    JsonTbl selectBy(Long id);
    JsonTbl selectLike(String name);
 
}

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="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