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"

}'

);

"root@localhost: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":"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('[10, 20, [30, 40]]', '$[1]');

"root@localhost: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;

"root@localhost: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", "jery@163.com", "age",33);

"root@localhost:mysql.sock [json]>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": [2, 3]}';

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

"root@localhost: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}');

"root@localhost: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);

"root@localhost: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;

"root@localhost: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;

"root@localhost: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','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; 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;