mysql处理json 比用like好 MySQL对JSON数据的增删改查

mysql官方文档：https://dev.mysql.com/doc/refman/8.0/en/json.html

建库建表增删改查

#删库
drop database RUNOOB;

#建库
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

#建表
CREATE TABLE `test` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`info` JSON
);





#增---------------------------
#key:value
INSERT INTO test(info) VALUES ('{"name":"chenhao"}');
#数组
INSERT INTO test(info) VALUES ('{"name":"Bill","Array":[1,2]}');




#改---------------------------
#全部覆盖
update gioet_instance set properties='{"name": "chentianle"}';
#改某个value
update test set info=json_set(info,"$.name","admin")
#删除JSON中多个key:value,数组等 其它不变
UPDATE test SET info = JSON_REMOVE(info, '$.name', '$.Array');





#查：重点---------------------------
#取出value 双>>去掉引号
select info->>'$.name', info->'$.Array[0]' from test;
#同上
select JSON_UNQUOTE(info->'$.name'), info->'$.Array[0]' from test;
#同上,取出value
select JSON_UNQUOTE(JSON_EXTRACT(info,'$.name')) from test;
#查含有key:value
select * from test where JSON_CONTAINS(info, JSON_OBJECT("name", "Bill"));





#删 JSON中key为name和Array，其它不变--------------------------
UPDATE test SET info = JSON_REMOVE(info, '$.name', '$.Array');

• 其它示例应用

#获取区域范围内所有客户管理员 
SELECT bu.* FROM gioet_instance AS p 
inner join blade_user as bu on bu.tenant_id=p.tenant_id and bu.real_name=JSON_UNQUOTE(JSON_EXTRACT(p.properties,'$.contact'))
WHERE p.category_id in(1,2) AND      (      JSON_UNQUOTE(JSON_EXTRACT(p.properties,'$.province')) IN ('1','2') or        JSON_UNQUOTE(JSON_EXTRACT(p.properties,'$.city')) IN ('1','2') or      JSON_UNQUOTE(JSON_EXTRACT(p.properties,'$.district')) IN ('1','2') or      JSON_UNQUOTE(JSON_EXTRACT(p.properties,'$.address')) IN ('1','2')    )


#更改地区中地区及管理员
update gioet_instance set 
properties='{"id": 10, "lat": "1", "lng": "1", "city": "1","contact": "admin", "memo": "", "district": "1", "province": "1", "entity_id": 10}'
where id=10;

 

 

#创建json对象 JSON_OBJECT("gatewayId", "20200001")
#判断properties是否包含某个josn    JSON_CONTAINS(properties, JSON_OBJECT("gatewayId", "20200001")) 
#提取json值 SELECT JSON_EXTRACT (字段名, '$.属性名')     示例select JSON_EXTRACT(properties,'$.gatewayId') from tb_iot where  cat_id=2 and pid=8032;
#提取json数组  SELECT JSON_EXTRACT (字段名, '$[0].属性名') 

 

 
select * from gioet_instance where JSON_CONTAINS(properties, JSON_OBJECT("gatewayId", "20200001"));

 

参考：

https://blog.csdn.net/qq_21187515/article/details/90760337