Mysql json类型
Mysql json类型5.7版本支持
原生json类型代替BLOB类型
json数据有效性检查
查询性能提升：不需要遍历所有字符串才能找到数据
支持部分属性索引
1. json格式范例
2. 结构化与非结构化
结构化：二维表结构（行和列） ◦ 使用SQL语句进行操作
非结构化：使用Key-Value格式定义数据，无结构定义 ◦ Value可以嵌套Key-Value格式的数据 ◦ 使用JSON进行实现
SQL创建User表
create table user (
id bigint not null auto_increment,
user_name varchar(10),
age int,
primary key(id) );
JSON定义的User表 ==类似mongodb文档数据库
db.user.insert({ user_name:"tom", age:30 })
db.createCollection("user")
3. json 操作例子
(1)json入门
创建带json字段的表
create table user (
uid int auto_increment,
data json,
primary key(uid));
插入json数据
insert into user values(
null,
'{"name":"tom",
"age":18,
"address":"sz"
}'
);
"root@localhost:mysql.sock>select * from user;
+-----+---------------------------------------------+
| uid | data |
+-----+---------------------------------------------+
| 1 | {"age": 18, "name": "tom", "address": "sz"} |
+-----+---------------------------------------------+
1 row in set (0.01 sec)
insert into user values(null,
'%7B" name> "age":28,
"mail":"jim@163.com"
br/>null,
'{"name":"jim",
"age":28,
"mail":"jim@163.com"
}'
);
insert into user values ( null, "can you insert it?");
4、json常用函数介绍
(1)json_extract
使用json_extract提取数据
原型 : JSON_EXTRACT(json_doc, path[, path] ...)
从list中抽取 下标 为1的元素（下标从0开始）
select json_extract(']', '$');
"root@localhost:mysql.sock>select json_extract(']', '$');
+--------------------------------------------+
| json_extract(']', '$') |
+--------------------------------------------+
| 20 |
+--------------------------------------------+
1 row in set (0.00 sec)
select json_extract(data, '$.name'),json_extract(data, '$.address')from user;
"root@localhost:mysql.sock>select json_extract(data, '$.name'),json_extract(data, '$.address')from user;
+------------------------------+---------------------------------+
| json_extract(data, '$.name') | json_extract(data, '$.address') |
+------------------------------+---------------------------------+
| "tom" | "sz" |
| "jim" | NULL |
+------------------------------+---------------------------------+
2 rows in set (0.00 sec)
(2)json_object
将list(K-V对)封装成json格式
原型 : JSON_OBJECT( ...])
select json_object("name", "jery", "email", "jery@163.com", "age",33);
"root@localhost:mysql.sock>select json_object("name", "jery", "email", "jery@163.com", "age",33);
+----------------------------------------------------------------+
| json_object("name", "jery", "email", "jery@163.com", "age",33) |
+----------------------------------------------------------------+
| {"age": 33, "name": "jery", "email": "jery@163.com"} |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
insert into user values (
null,
json_object("name", "jery", "email", "jery@163.com", "age",33)
);
(3)json_insert
插入数据
原型 : JSON_INSERT(json_doc, path, val[, path, val] ...)
set @j = '{ "a": 1, "b": }';
select json_insert(@j, '$.a', 10, '$.c', '');
"root@localhost:mysql.sock>select json_insert(@j, '$.a', 10, '$.c', '');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '') |
+----------------------------------------------------+
| {"a": 1, "b": , "c": ""} |
+----------------------------------------------------+
1 row in set (0.00 sec)
update user set data = json_insert(data, "$.address_2", "BJ") where uid = 1;
select * from user;
(4)json_merge
合并数据并返回。注意：原数据不受影响
原型 : JSON_MERGE(json_doc, json_doc[, json_doc] ...)
-- 原来有两个JSON
select json_merge('{"name": "x"}', '{"id": 47}');
"root@localhost:mysql.sock>select json_merge('{"name": "x"}', '{"id": 47}');
+-------------------------------------------+
| json_merge('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------+
| {"id": 47, "name": "x"} |
+-------------------------------------------+
1 row in set (0.00 sec)
(5)json_array_append
追加数据 --
原型 : JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
-- json_append 在5.7.9 中重命名为 json_array_append
set @j = '["a", ["b", "c"], "d"]'; -- 下标为1的元素中只有["b", "c"]
select json_array_append(@j, '$', 1);
"root@localhost:mysql.sock>select json_array_append(@j, '$', 1);
+----------------------------------+
| json_array_append(@j, '$', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+
1 row in set (0.00 sec)
(6)json_remove
从json记录中删除数据
-- 原型 : JSON_REMOVE(json_doc, path[, path] ...)
set @j = '["a", ["b", "c"], "d"]';
select json_remove(@j, '$');
update user set data = json_remove(data, "$.address_2") where uid = 1;
5. json创建索引
JSON 类型数据本身 无法直接 创建索引，
需要将需要索引的JSON数据重新生成虚拟列(Virtual Columns) 之后，对该列进行索引
(1)新建表时创建JSON索引
#抽取data中的name， 生成新的一列，名字为gen_col并将gen_col 作为索引
create table test_inex_1(
data json,
gen_col varchar(10) generated always as (json_extract(data, '$.name')),
index> );
show create table test_inex_1;
"root@localhost:mysql.sock>show create table test_inex_1\G;
1. row
Table: test_inex_1
Create Table: CREATE TABLE test_inex_1 (
data json DEFAULT NULL,
gen_col varchar(10) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL,
KEY idx (gen_col)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
insert into test_inex_1(data) values ('{"name":"tom", "age":18, "address":"SH"}');
insert into test_inex_1(data) values ('{"name":"jim", "age":28, "address":"SZ"}');
select * from test_inex_1;
"root@localhost:mysql.sock>select * from test_inex_1;
+---------------------------------------------+---------+
| data | gen_col |
+---------------------------------------------+---------+
| {"age": 18, "name": "tom", "address": "SH"} | "tom" |
| {"age": 28, "name": "jim", "address": "SZ"} | "jim" |
+---------------------------------------------+---------+
2 rows in set (0.00 sec)
select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"tom"';-- 使用'"tome"',用单引号括起来
explain select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"tom"'\G
(2)修改已存在的表创建JSON索引
show create table user;
select from user;
alter table user add user_name varchar(32) generated always as (json_extract(data,"$.name")) virtual;
select user_name from user;
alter table user add index> selectfrom user where user_name='"tom"';-- 加单引号
explain select * from user where user_name='"tom"'\G
6. 附录
-- 老师演示JSON的SQL
-drop table if exists User;
CREATE TABLE User (
uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
email VARCHAR(256) NOT NULL,
address VARCHAR(512) NOT NULL,
UNIQUE KEY (name),
UNIQUE KEY (email) );
INSERT INTO User VALUES (NULL,'David','david@gmail','Shanghai ...'); INSERT INTO User VALUES (NULL,'Amy','amy@gmail','Beijing ...');
INSERT INTO User VALUES (NULL,'Tom','tom@gmail','Guangzhou ...');
SELECT * FROM User;
ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL;> DROP TABLE IF EXISTS UserJson;
CREATE TABLE UserJson(
uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data JSON );
truncate table UserJson;
insert into UserJson SELECT uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data FROM User;
SELECT * FROM UserJson;
SELECT uid,JSON_EXTRACT(data,'$.address2') from UserJson;
UPDATE UserJson set data = json_insert(data,"$.address2","HangZhou ...") where uid = 1;
SELECT JSON_EXTRACT(data,'$.address') from UserJson;
select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) from UserJson;
begin;
UPDATE UserJson set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2')) where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;
select JSON_EXTRACT(data,'$.address') from UserJson;
UPDATE UserJson set data = JSON_REMOVE(data,'$.address2') where uid>0;
commit;
页:
[1]