MySQL中存储json格式数据
1.1.1. JSON类型数据存储
新建表
create table json_user (
uid int auto_increment,
data json,
primary key(uid)
);
插入数据
insert into json_user values (
null, '{
"name":"lison",
"age":18,
"address":"enjoy"
}' );
insert into json_user values (
null,
'{
"name":"james",
"age":28,
"mail":"james@163.com"
}');
1.1.1.2. JSON函数
1.1.1.2.1. json_extract 抽取
select json_extract('[10, 20, [30, 40]]', '$[1]');
-- 10
select
json_extract(data, '$.name'),
json_extract(data, '$.address')
from json_user;
--取出json类型字段中，name跟address
1.1.1.2.2. JSON_OBJECT 将对象转为json
select json_object("name", "enjoy", "email", "enjoy.com", "age",35);
insert into json_user values (
null,
json_object("name", "王五", "email", "wangwu@qq.com", "age",18) );
1.1.1.2.3. json_insert 插入数据
语法：JSON_INSERT(json_doc, path, val[, path, val] ...)
set @json = '{ "a": 1, "b": [2, 3]}';
-- @json局部 ； -- @@json全局
select json_insert(@json, '$.a', 10, '$.c', '[true, false]');
-- 当前语句：更新或者修改（数据已存在更新，没有的插入）
update json_user set data = json_insert(data, "$.address_2", "xiangxue") where uid = 1;
1.1.1.2.4. json_merge 合并数据并返回
select json_merge('{"name": "enjoy"}', '{"id": 47}');
select
json_merge(
json_extract(data, '$.address'),
json_extract(data, '$.address_2')
)
from json_user where uid = 1;
-- 将当前用户的两个地址合并
1.1.1.2.5. 其他函数：
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
1.1.2. JSON索引
JSON 类型数据本身 无法直接 创建索引，需要将需要索引的 JSON数据 重新 生成虚拟列(Virtual Columns) 之后，对 该列 进行 索引
create table test_inex_1(
data json,
gen_col varchar(10) generated always as (json_extract(data, '$.name')),
index idx (gen_col)
);
insert into test_inex_1(data) values ('{"name":"king", "age":18, "address":"cs"}');
insert into test_inex_1(data) values ('{"name":"peter", "age":28, "address":"zz"}');
select * from test_inex_1;
疑问：这条sql查询的结果是？
select json_extract(data,"$.name") as username from test_inex_1 where gen_col="king";
select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"king"';
explain select json_extract(data,"$.name") as username from test_index_1 where gen_col="king"
查阅官方文档，建立虚拟列，这个列查询的时候不需要加上“”符号
create table test_index_2 (
data json,
gen_col varchar(10) generated always as (
json_unquote(
json_extract(data, "$.name")
)),
key idx(gen_col)
);
insert into test_index_2(data) values ('{"name":"king", "age":18, "address":"cs"}');
insert into test_index_2(data) values ('{"name":"peter", "age":28, "address":"zz"}');
select json_extract(data,"$.name") as username from test_index_2 where gen_col="king";
