mysql 5.7+ 版本开始支持 json 数据类型,可以方便的存储JSON格式的数据,同时配合虚拟列 (virtual generated column),可以方便的为 json 列数据的某属性映射虚拟列,建立索引,高效检索。
构造json数据
方法:json_array() / json_object()
json_array / json_object 用于组装 json 数据,json 说的简单些json就是由标量(int, float, string) + 数组 + 对象组合而成的,这两个函数可以方便的用于构造数组和对象的json格式串
-
json_array(item1, item2, item3, ...)
-
=>
-
[item1, item2, item3]
-
json_object(key1, val1[, [key2, val2]...])
-
=>
-
{"key1": "val1", "key2": "val2",...}
使用场景例如:
-
select json_object(
-
"username", "big_cat",
-
"favorites", json_array(
-
json_object("article_id", 1, "favorited_at", "2019-01-18"),
-
json_object("article_id", 2, "favorited_at", "2019-01-18"),
-
json_object("article_id", 3, "favorited_at", "2019-01-18"),
-
json_object("article_id", 4, "favorited_at", "2019-01-18")
-
)
-
);
-
// result
-
{
-
"username": "big_cat",
-
"favorites": [
-
{"article_id": 1, "favorited_at": "2019-01-18"},
-
{"article_id": 2, "favorited_at": "2019-01-18"},
-
{"article_id": 3, "favorited_at": "2019-01-18"},
-
{"article_id": 4, "favorited_at": "2019-01-18"}
-
]
-
}
读取json数据
方法:json_extract() /col
->"$.{property_name}"
json_extract 用于读取 json 列的某字段,或者也可以使用 col
->"$.{property_name}" 的方式访问
json_extract(`col`, '$.{property_name}') / `col`->'$.{property_name}'
-
create table `users` (
-
`id` int unsigned not null auto_increment primary key,
-
`doc` json
-
);
-
insert into `users`(`doc`)
-
values (json_object("name", "big_cat", "age", 28)), ('{"name": "james", "age": 29}');
-
select json_extract(`doc`, "$.name") as `name`, json_extract(`doc`, "$.age") as `age` from `users`;
-
select `doc`->"$.name" as `name`, `doc`->"$.age" as `age` from `users`;
高效检索json数据
mysql 提供的一些函数是可以方便我们条件检索json数据的,但无法使用索引,数据量大的时候难免低效。
-
select id, doc->"$.age" from users where json_extract(doc, "$.name") = "big_cat";
-
select id, doc->"$.age" from users where doc->"$.name" = "big_cat";
这时我们可以利用同 json 一同新增的特性:虚拟列(virtual generated column)。
将需要参与检索的 json 属性映射为 虚拟列,在虚拟列上建立索引,便可参与高效检索。
另外补充一下,在mysql 5.7+中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column。前者不存储元数据,后者会将 expression 的计算结果实际的存储下来。其实二者性能差距并不大,若对二者建立索引进行检索操作,前者性能可能会略低于后者,因为前者要对结果集即时的进行 expression 的演算,但后者需要消耗额外的存储空间。
需要注意的有:不存储数据的特性也导致只能在虚拟列上建立二级索引,插入数据时不可以向虚拟列插入数值(mysql自行负责演算)。
-
#虚拟列创建
-
ALTER TABLE `table_name` ADD COLUMN `col_name` <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
-
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
-
# 为 user 表的 json 字段的 name 创建虚拟列
-
alter table `users` add column `user_name` varchar(10) generated always as (`doc`->"$.name");
-
# 为虚拟列添加索引
-
alter table `users` add index `index_u_n`(`user_name`);
-
# 检索时可以使用索引
-
explain select `id`, `user_name`, `doc`->"$.age" as `age` from `users` where `user_name` = "big_cat" \G
-
*************************** 1. row ***************************
-
id: 1
-
select_type: SIMPLE
-
table: users
-
partitions: NULL
-
type: ref
-
possible_keys: index_u_n
-
key: index_u_n
-
key_len: 43
-
ref: const
-
rows: 1
-
filtered: 100.00
-
Extra: NULL
-
1 row in set, 1 warning (0.00 sec)
下面直接对 json 解析检索的方式是无法用到索引的
-
explain select id from users where doc->"$.user_name" = "big_cat" \G
-
*************************** 1. row ***************************
-
id: 1
-
select_type: SIMPLE
-
table: users
-
partitions: NULL
-
type: ALL
-
possible_keys: NULL
-
key: NULL
-
key_len: NULL
-
ref: NULL
-
rows: 2
-
filtered: 100.00
-
Extra: Using where
-
1 row in set, 1 warning (0.00 sec)
-
explain select id from users where json_extract(`doc`, '$.username') = "big_cat" \G
-
*************************** 1. row ***************************
-
id: 1
-
select_type: SIMPLE
-
table: users
-
partitions: NULL
-
type: ALL
-
possible_keys: NULL
-
key: NULL
-
key_len: NULL
-
ref: NULL
-
rows: 2
-
filtered: 100.00
-
Extra: Using where
-
1 row in set, 1 warning (0.00 sec)
还有其他 json 的使用这里就不说明了,大家可以参考一下文章:
mysql json 使用 类型 查询 函数:https://www.cnblogs.com/ooo0/...
MySQL 5.7 虚拟列 (virtual columns):https://www.cnblogs.com/raich...
MySQL 5.7原生JSON格式支持:https://www.cnblogs.com/zouca...