mysql 5.7 json type

Posted on | Edited on | In

none structure data in mysql

in mysql 5.7 , support store data in json type.

create table with virtual column.

1
2
3
4
5
6
CREATE TABLE players (  
   id INT UNSIGNED NOT NULL primary key auto_increment,
   player JSON NOT NULL,
   -- name virtual column
   vname VARCHAR(50) GENERATED ALWAYS AS (`player` ->> '$.name') NOT NULL
);
1
SHOW COLUMNS FROM `players`;

prepare save progress

1
2
3
4
-- check whether store procedure open
show variables like 'log_bin_trust_function_creators';
-- open store procedure in mysql
set global log_bin_trust_function_creators=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter $$
create procedure insert_player(in max_num int(10))
begin
 declare i int default 0;
 declare json_data varchar(2000) default '1';
 set autocommit= 0;
 repeat
 set i=i+1;
 set json_data = concat(concat('{"name":"xxx-',i),'","age":34}');
 insert into players (id,player) values(null,json_data);
 until i=max_num end repeat;
commit;
end $$

test data, and try

1
call insert_player(2000000);
1
EXPLAIN SELECT * FROM `players` WHERE `vname` = "xxx-990099"
1
CREATE INDEX `name_idx` ON `players`(`vname`);
1
EXPLAIN SELECT * FROM `players` WHERE `vname` = "xxx-990099"

https://blog.csdn.net/bugs4j/article/details/79932538