66.mysql的json语法

Mysql的json语法：

# 创建json表
create table t_json(id int primary key ,sname varchar(20),info json);
# 插入json数据
insert into t_json(id,sname,info) values(1,'name1',json_array(1,'abc',null,true,curtime()));
insert into t_json(id,sname,info) values(2,'sname2',JSON_OBJECT("age",20,"time",now()));
insert into t_json(id,sname,info) values(3,'name3','{"age":20,"time": "2020-02-14 10:52:00"}');
select * from t_json;

# 查询
select sname,JSON_EXTRACT(info,'$.age') from t_json;
select id,json_keys(info) from t_json;
select sname,info->'$.age' from t_json;
# 修改记录
update t_json set info=json_set(info,'$.ip','192.168.1.1') where id=2;
update t_json set info=json_set(info,'$.ip','192.168.1.2') where id=2;
update t_json set info = json_remove(info,'$.ip') where id =2; #删除