五、Mysql中JSON类型

前文-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('[10, 20, [30, 40]]', '$[1]');

select json_extract(data, '$.name'), json_extract(data, '$.address')
from json_user;

JSON_OBJECT 将对象转为 json

SELECT JSON_OBJECT("name", "enjoy", "email", "enjoy.com", "age",35);

INSERT INTO json_user VALUES ( NULL, JSON_OBJECT("name", "enjoy", "email", "enjoy.com", "age",35) );

json_insert 插入数据

语法：JSON_INSERT(json_doc, path, val[, path, val] …)

set @json = '{ "a": 1, "b": [2, 3]}';

select json_insert(@json, '$.a', 10, '$.c', '[true, false]');


update json_user set data = json_insert(data, "$.address_2", "xiangxue") where uid = 1;

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;

其他函数：

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

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_inex_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”;