Mysql使用json数据类型

Json类型使用

基础使用

1. 创建表

create table json_user (
uid int auto_increment, data json, primary key(uid)
);

2. 插入数据

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类型