Mysql json類型

標籤：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"
}‘
);

"[email protected]:mysql.sock  [json]>select * from user;+-----+---------------------------------------------+| uid | data                                        |+-----+---------------------------------------------+|   1 | {"age": 18, "name": "tom", "address": "sz"} |+-----+---------------------------------------------+1 row in set (0.01 sec)

insert into user values("age":28,
"mail":"[email protected]"
br/>null,
‘{"name":"jim",
"age":28,
"mail":"[email protected]"
);

insert into user values ( null, "can you insert it?"); -- 無法插入，因為是JSON類型

4、json常用函數介紹

(1)json_extract
使用json_extract提取資料
原型 : JSON_EXTRACT(json_doc, path[, path] ...)
從list中抽取 下標 為1的元素（下標從0開始）
select json_extract(‘[10, 20, [30, 40]]‘, ‘$[1]‘);

"[email protected]:mysql.sock  [json]>select json_extract(‘[10, 20, [30, 40]]‘, ‘$[1]‘); +--------------------------------------------+| json_extract(‘[10, 20, [30, 40]]‘, ‘$[1]‘) |+--------------------------------------------+| 20                                         |+--------------------------------------------+1 row in set (0.00 sec)

select json_extract(data, ‘$.name‘),json_extract(data, ‘$.address‘)from user;

"[email protected]:mysql.sock  [json]>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([key, val[, key, val] ...])
select json_object("name", "jery", "email", "[email protected]", "age",33);

"[email protected]:mysql.sock  [json]>select json_object("name", "jery", "email", "[email protected]", "age",33); +----------------------------------------------------------------+| json_object("name", "jery", "email", "[email protected]", "age",33) |+----------------------------------------------------------------+| {"age": 33, "name": "jery", "email": "[email protected]"}           |+----------------------------------------------------------------+1 row in set (0.00 sec)

insert into user values (
null,
json_object("name", "jery", "email", "[email protected]", "age",33)
);

(3)json_insert
插入資料
原型 : JSON_INSERT(json_doc, path, val[, path, val] ...)

set @j = ‘{ "a": 1, "b": [2, 3]}‘;
select json_insert(@j, ‘$.a‘, 10, ‘$.c‘, ‘[true, false]‘);

"[email protected]:mysql.sock  [json]>select json_insert(@j, ‘$.a‘, 10, ‘$.c‘, ‘[true, false]‘); +----------------------------------------------------+| json_insert(@j, ‘$.a‘, 10, ‘$.c‘, ‘[true, false]‘) |+----------------------------------------------------+| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |+----------------------------------------------------+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}‘);

"[email protected]:mysql.sock  [json]>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]‘, 1);

"[email protected]:mysql.sock [json]>select json_array_append(@j, ‘$[1]‘, 1);
+----------------------------------+
| json_array_append(@j, ‘$[1]‘, 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, ‘$[1]‘);
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 idx (gen_col)
);

show create table test_inex_1;

"[email protected]:mysql.sock [json]>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;

"[email protected]:mysql.sock  [json]>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 idx(user_name);
select from 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‘,‘[email protected]‘,‘Shanghai ...‘); INSERT INTO User VALUES (NULL,‘Amy‘,‘[email protected]‘,‘Beijing ...‘);
INSERT INTO User VALUES (NULL,‘Tom‘,‘[email protected]‘,‘Guangzhou ...‘);
SELECT * FROM User;
ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL; ALTER TABLE User ADD COLUMN passport VARCHAR(64) 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[1]‘) 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;

Mysql json類型