Mysql中json数据类型简单使用
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"}');
Json函数
json_extract 抽取
select JSON_EXTRACT(data, '$.name'),JSON_EXTRACT(data, '$.address') from json_user
JSON_OBJECT 将对象转为 json
insert into json_user values(null,json_object("name", "zls", "email", "xxx@qq.com", "age",3))
json_insert 插入数据
update json_user set data = json_insert(data, "$.address_2", "beijing") where uid = 1;
json_merge 合并数据并返回
select json_merge(json_extract(data,'$.address_2'),json_extract(data,'$.name')) from json_user where uid =
json_replace替换数据
update json_user set data = json_replace(data,'$.address_2',"上海") where uid = 1;
其他函数
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
JSON 索引
JSON 类型数据本身 无法直接 创建索引,需要将需要索引的 JSON 数据重新生成虚拟列(Virtual Columns) 之后,对该列进行索引
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"}');
explain select json_extract(data,"$.name") as username from test_index_2 where gen_col='king'
JSON_UNQUOTE函数就是去掉引号的功能,将原json串的引号去掉转成string类型