使用数据库来操作json

1 操作json

1-1 创建表

create table tab_json(id int not null auto_increment primary key,data json);

1-2 新增

insert into tab_json values (null,'{"name":"Mike","address":"Beijing","tel":13249872314}');
insert into tab_json values (null,'{"name":"David","address":"Shanghai","tel":189776542}');

1-3 删除

1-4 修改

如果存在就进行替换,如果不存在键值,就增加键值对.

update tab_json set data = json_set(data,"$.address","Guangzhou") where id = 1;

1-5 查询

select  * from tab_json where data->'$.id' = 142;

select distinct(property->'$.name')  name from stu;