准备工作

使用docker创建一个mysql实例

docker pull mysql:latest
# 拉取最新的mysql镜像

docker run --name mysql8 -p 13306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:latest
# 启动镜像，使用宿主机的13306端口映射3306、root用户的密码也设置

docker exec -it mysql8 /bin/bash
# 进入docker mysql实例

mysql -u root -p root
# 使用root用户连接mysql 

set time_zone = '+8:00';
set global time_zone = '+8:00';
flush privileges;
# 设置下时区

select now();
# 查询当前时间显示正常就OK了

创建一个最简单的表

create table t1 (id bigint(20) primary key auto_increment, `doc` json);


insert into t1 (doc) value ('["a", "b"]');
insert into t1 (doc) value ('["c", "d"]');
insert into t1 (doc) value ('["e", "f"]');
insert into t1 (doc) value ('{"name":"zhangsan","age":22}');
insert into t1 (doc) value ('{"name":"lisi","age":23}');
insert into t1 (doc) value ('{"name":"wangwu","age":23,"addr":["beijing","shanghai"]}');
insert into t1 (doc) value ('{"name":"fengliu","age":23,"addr":["qingdao","nanjing"]}');

select * from t1;

查询类函数

json_contains(json, '指定元素', [path]) 判断json中包含指定元素

数组

• 查询数组中包含指定元素的

select * from t1 where json_contains(`doc`, '["f"]');

对象

对象中属性等于给定值的

select * from t1 where json_contains(`doc`, '{"age":22}');

复杂json中嵌套对象，使用路径指定查询

复杂的json存在嵌套，对象中包含对象、对象中包含数组等，这种情况可以使用指定的路径进行查询

select * from t1 where json_contains(`doc`, '["beijing"]', '$.addr');

json_extract(json, path) 从json中提取指定路径的值

用在where条件后面做判断

select * from t1 where json_extract(`doc`, '$.name') = 'zhangsan';

用在select 之后获取查询记录的指定属性

select json_extract(`doc`, '$.age') from t1

json_extract 的别名 “->”

select id, `doc`->'$.age' as age from t1 where doc->'$.name' = 'wangwu';

json_keys 查询json中所有的key

select id, json_keys(doc) from t1;

select id, json_keys(doc, '$.lang') from t1 where id = 8;

修改类函数

json_set 修改json的值，如果有对应的属性则覆盖没有则添加

update t1 set doc = json_set(`doc`, '$.gongsi', 'kuaishou');

array类型的json没有添加进去，看来json_set只能用来操作object类型的json

json_set 设置一个key对应一个对象

如果直接写json会有点问题，会将直接设置成string

update t1 set doc = json_set(`doc`, '$.gongsi', '["kuaishou", "zijie"]');

需要配合cast函数对json字符串做转移

update t1 set doc = json_set(`doc`, '$.gongsi', cast('["kuaishou", "zijie"]' as json));

json_array_append向json数组中追加元素

注意参数json、path、需要追加的元素

update t1 set doc = json_array_append(`doc`, '$', 'z');

这里没有加限制条件，将object类型的json都转换成了json array，利用上面的几个函数将数据类型还原，一行sql将json修改成数组的第一个元素

update t1 set doc = `doc`->'$[0]' where id >= 4;

json_remove删除元素

update t1 set doc = json_remove(`doc`, '$.gongsi') where id = 8;

最后一行的gongsi 字段去掉了

 将路径修改成 $[1] 这样的形式可以删除数组中的元素

为JSON类型字段创建索引

ALTER TABLE t1 ADD COLUMN name VARCHAR(32) GENERATED ALWAYS AS (`doc` ->> '$.name') VIRTUAL;
ALTER TABLE t1 ADD INDEX idx_name(`name`);

# 查看建表yu'ju
show create table t1;

CREATE TABLE `t1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `name` varchar(32) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$.name'))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

注意这里的 ->> 等同于 JSON_UNQUOTE(JSON_EXTRACT()) 会将转移字符编译之后展示。

使用存储过程向表中放点数据

drop procedure if exists t1_insert_data;

delimiter $

create procedure t1_insert_data()

begin

    declare i int default 100;

    while i <= 99999999 do

        insert into t1(doc) value (concat('{"name":', i, ',"age":22}'));

        set i = i+1;

        end while

查看下索引的使用

select * from t1 where `doc`->'$.name' = '9999';

使用执行计划看看是否用到了索引

explain select * from t1 where `doc`->'$.name' = '9999';

结果使用到了索引。

非常完美，以后表中的data字段用起来就更加方便了。

https://dev.mysql.com/doc/refman/8.0/en/json-functions.html