MySQL Json 类型使用笔记

本文最后更新于：2024年6月20日晚上

最近在公司项目中开发时使用了 MySQL 的 json 字段类型来设计表，但是由于此前没有怎么好好用过这个类型，所以有了本篇的笔记，记录学习的过程。

一、初步使用

字段定义

json 在 5.7.8 开始对 json 进行支持的操作（隔壁的 postgresql 则是从 9.2 开始支持 json 类型的支持）。
下面是 json 字段的定义方式。

create table user(
  id not null primary key auto_increment,
  info json
);

其中，info 字段是 json 类型，如果想让字段有默认值，那么只能设定为 null 。

操作符

在操作 json 的过程当中经常会使用到一些操作符，在此文章的后面内容中也会经常用到，此小节先记录这些操作符的使用方法。
example ：

1 2	`set @json_object = '{"name": "mike", "age": 20}'; set @json_list = '["hello", 20]';`

->/->> ：访问 json 对象中的某个 key 或所有 key 的值，使用前面的操作符取出来的如果是字符串，那么会保留字符串里面的引号。如果想去除取出的字符串的引号，那么使用后面的操作符即可。这个方法不能单独使用，需要配合下面的操作符使用。

select b -> '$.name' from (select @json_object as b) as f;
-- "mike"
select b ->> '$.name' from (select @json_object as b) as f;
-- mike

$ ：这个需要和上面一起搭配使用，用于取出单个，或多个 key 中的元素，或者是访问列表中的对象。

$.name 取出当前层级的 name 的值
$.* 取出当前层级的每个 key 的值，存入一个列表中
$.*.* 取出第二层级所有的值的子对象的值，像这样的可以以此类推，写很多个层级
$.name.* 取出 key 为 name 的值里的对象的所有 value
$**.* 取出所有层级的所有的 value
$[*] 取出列表里面的所有元素
$[0] 取出列表里面第一个元素
$[*].* 取出列表所有元素的所有的 value (如果列表里面有对象数据)

注：使用通配符取出来的数据都是以列表格式展示的

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';

-- $**.*  取出每个对象里面所有的元素
select b -> '$**.*'
from (select @json_object as b) as f;
/**
[20, {"address": "beijing"}, [{"age": 20, "name": "jack"}], {"email": "xxx", "phone": "xxx"}, 
["football", "basketball", 20], "beijing", 20, "jack", "xxx", "xxx"]
**/

查询函数

json_contains(json_doc, val, [path]) ：判断给定的值是否在给定 json 数据的条件路径中，如果存在则返回1，否则返回0
注：此方法如果是数字要加单引号，如果是字符要在单引号的基础上加双引号。path 不能携带通配符，需要使用全路径。

1 2	`set @json_obj = '{"age": 18, "name": "zack"}'; select json_contains(@json_obj, '18', '$.age')`

json_contains_path(doc:json, one_or_all:varchar, path:varchar*) 判断 json 数据中是否存在匹配的一个或多个指定路径，参数 one_or_all 表示指定 one 时匹配到一个就返回1，指定 all 时必须所有的指定路径都匹配才返回1。

1
2
3

set @json_obj = '{"age": 18, "name": "zack"}';
select json_contains_path(@json_obj, 'all', '$.name', '$.info')
-- 0

json_extract(doc:json, path:varchar*) 从给定的 json 数据中根据给定的表达式提取值。此方法就等同于使用上面的 -> 一样，可以使用操作符替换
如果取出的字符串还携带引号，那么可以再外面加一层 json_unquote 函数，把引号给去除掉

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';

select json_extract(@json_object, '$.name');
-- {"address": "beijing"}

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 从给定的 json 数据中搜索指定的 json 字符串，并且还可以指定搜索位置，如果找到则返回 json path 的位置，可能会有一个或多个，如果是多个，那么返回的是一个数组，如果是一个，那么返回的是一个字符串。需要注意的是，搜索的是 value 的值，或者是列表里面的元素。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';

select json_search(@json_object, 'one', 'jack', NULL, '$.friend');
-- "$.friend[0].name"

json_keys(doc:json, [, path:varchar]) 从给定的 json 数据中取出指定 path 里所有对象的 key 的值，结果为列表展示。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';

-- 取出当前这层的 keys
select json_keys(@json_object, '$');
-- ["age", "name", "friend", "contact", "hobbies"]

插入/创建/新增/删除函数

json_object 此函数用于创建一个存放 key-value 的 json 数据对象。

1	`set @json_object = json_object('{"name": "mike", "age": 20}');`

json_array 则用于创建一个存放列表元素的 json 数据列表。

1	`set @json_list = json_array('hello', 12)`

json_insert/json_array_insert 前者针对 json 对象，json 列表数据，在给定的路径中加入指定元素数据，如果元素存在则不做任何操作，否则添加元素，并且返回一个新的 json 数据。
前者的函数可以适用于存储的格式是 json 对象和列表上，后者的方法只能用于存储的格式是列表数据上。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';

set @Json_list = '["11", "a"]';

select json_insert(@json_object, '$.c', convert('{"foss":"hello"}', json));

select json_array_insert(@Json_list, '$[4]', 20);

json_array_append 和这个函数相关的是一个叫 json_append 的函数，不过这个函数已经被废弃，并且改名叫 json_array_append 函数。这个函数用于对存储的格式是对象和列表的 json 数据同样有效，执行后返回新的 json 数据。

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);

SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');

json_replace 从给定的 json 中替换数据，存在则替换，否则不会进行任何操作，可以替换多个路径下的值。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}], "school": "no"}';

select json_replace(@json_object, '$.age', null);

json_set 从给定的 json 中替换数据，和上面不一样的是，无论指定的路径存在或是不存在，都会使用函数里指定的值进行替换或是创建。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["football", "basketball", 20],
"friend": [{"name": "jack", "age": 20}], "school": "no"}';
select json_set(@json_object, '$.hello', 1);

json_merge(doc:json, doc:json*) 合并多个 json 数据到一起，如果数据是列表，那么会组合在一起，如果数据是对象，那么会把对象作为一个列表元素，和其他的列表组合在一起。
如果合并的对象 key 相同，那么值会作为 value 和这个 key 的其他值一起组成一个列表。

set @json_object = '{"name": {"address": "beijing"}, "age": 20, "contact":
{"phone": "xxx", "email": "xxx"}, "hobbies": ["basketball", 20],
"friend": [{"name": "jack", "age": 20}]}';
set @Json_list = '["11", "a"]';
select json_merge(@json_object, '{"name": "f"}', @Json_list);
/**
[{"age": 20, "name": [{"address": "beijing"}, "f"], "friend": [{"age": 20, "name": "jack"}], 
"contact": {"email": "xxx", "phone": "xxx"}, "hobbies": ["basketball", 20]}, "11", "a"]
**/

json_merge_path(json_doc,json_doc[,json_doc] ...) 这个函数和上面的稍微有点不同，有以下几个规则。

如果第一个参数不是对象，则合并的结果与空对象与第二个参数合并时的结果相同。
如果第二个参数不是对象，则 merge 的结果是第二个参数。
如果两个参数都是对象，则 merge 的结果是具有以下成员的对象:
- 第一个对象的所有成员在第二个对象中没有具有相同键的对应成员。
- 第二个对象的所有成员在第一个对象中没有相应的键，其值不是 JSON null 文本。
- 所有成员的键都存在于第一个和第二个对象中，并且其值在第二个对象中不是 JSON null 文本。这些成员的值是将第一个对象中的值与第二个对象中的值递归合并的结果。

SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
-- [true, false]
SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}'); 
-- '{"id": 47}'

json_remove(json_doc, path[,path]..) 从给定的 json 数据中移除指定路径的数据，如果存在则移除，否则不做任何操作。

1
2
3

SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j, '$[1]');
-- ["a", "d"]

辅助函数

json_type 判断给定的 json 数据是什么类型。
json_valid 校验给定的 json 数据是否合法，如果合法则返回1，否则返回0。
json_length(json_doc, [,path]) 从给定的 json 数据中根据指定的位置返回当前位置下的 json 长度。
json_dept(json_doc) 判断给定的 json 文档的深度，返回 json 的深度，也就是层级。

索引相关

MySQL 在 5.7.8 和 8.0 上对 JSON 添加索引的方式有所不同。

5.7.8 版本

表结构如下：

create table project(
  id int primary key auto_increment,
  bind_dept json
)

# json 字段存储的是如下的数据格式。
'{"deptId": 12, "deptName": "xxx"}'

在 5.7.8 中采用虚拟列的方式，其实就是间接的引用了 json 数据中要索引的字段，进行存储，然后再根据虚拟列创建索引，在存储 json 数据的时候，会自动在虚拟列插入数据。查询的时候可以使用 json 的方式查询 key，或者直接使用虚拟列进行查询。

1 2	`ALTER TABLE project ADD COLUMN deptId int(5) AS (bind_dept->>"$.deptId"); ALTER TABLE project ADD UNIQUE INDEX index_deptId(deptId);`

验证是否用到索引：

explain select * from project where bind_dept -> '$.deptId' = 12;
explain select * from project where deptId = 12;
/**
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+--------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra  |
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | project | <null>     | const | index_deptId  | index_deptId | 5       | const | 1    | 100.0    | <null> |
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+--------+
**/

在 8.0 中使用

8.0 针对 mysql 数组新增了多值索引的概念，下面是例子。

# 如果存储的是单个对象，那么使用下面语句创建 json 索引
-- 假设格式
-- '{"code": 11, "value": 'xxx'}'
ALTER TABLE project ADD INDEX idx_info_list( ( CAST( json_info -> '$.code' AS UNSIGNED array)) );
# 如果存储的是列表数据，里面存储的是格式一样的对象元素，使用下面的语句创建索引
-- 假设格式
-- '[{"code": 111, "name": "xxx"}]'
ALTER TABLE project ADD INDEX idx_info_list( ( CAST( json_info -> '$[*].code' AS UNSIGNED array)) );
# 如果想让 json 索引和普通索引一起使用，也就是多值索引，那么使用下面的语句创建
-- 格式如下
-- '[{"code": 111, "name": "xxx"}]'
-- 其他的字段 age
ALTER TABLE project ADD INDEX idx_info__age_list(age,( CAST( json_info -> '$[*].code' AS UNSIGNED array)) );

根据需要可以使用上面的格式创建三种索引，然后可以使用 explain 验证是否使用到索引。

二、在代码中映射 json 字段

这里记录如何在 SpringBoot 项目中对 MySQL json 字段的映射支持。
我在项目里使用的是 MyBatis Plus，下面是映射方式。
首先是字段定义，需要按照如下设置，在指明字段映射时，需要使用 typeHandler 指定数据库字段类型处理。

@Data
@Accessors(chain = true)
@TableName(value = "warning_project")
public class WarningProject implements Serializable {

    @TableId(type = IdType.AUTO)
    private Integer id;

    .....

    @TableField(value = "bind_dept", typeHandler = JacksonTypeHandler.class)
    private List<BindDept> bindDept;
}

然后是 xml 中的配置，如果是写了自定义 xml，那么也需要使用 typeHandler 处理。

<resultMap id="project" type="com.root2z.domain.WarningProject">
    <id property="id" column="id"/>
    ......
    <result property="bindDept" column="bind_dept"
            typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
</resultMap>

三、后记

在使用 json 字段的过程中，发现对于一些数据查找，连表的方式还是有点复杂，所以在设计的时候，就应该要考虑好这个字段存储的数据，以及复杂程度，最好是存储一些不易变动的数据，例如用户信息什么的。

数据库

#Java #MySQL

MySQL Json 类型使用笔记

http://aim467.github.io/2024/06/20/MySQL-Json-类型使用笔记/

作者

Dedsec2z

发布于

2024年6月20日

更新于

2024年6月20日

许可协议

EasyExcel 动态导出上一篇

一次js逆向实践下一篇

MySQL Json 类型使用笔记

一、 初步使用

字段定义

操作符

查询函数

插入/创建/新增/删除 函数

辅助函数

索引相关

二、在代码中映射 json 字段

三、后记

一、初步使用

插入/创建/新增/删除函数