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->pathJSON_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
