MySQL 操作

操作 json类型

INSERT INTO json_demo ( id, address )VALUES ( 1, '{ "1" : "河南", "2" : "郑州", "3" : "金水" }' )
	
insert into json_demo (id, payment_method, address       )     values (5, {"1":1,"2":2,"3":3}, {"1":"河南","2":"郑州","3":"金水"}       )
	
select * from json_demo where  address->'$.oneid' = '1';
-- 	如果用不等于做判断条件，只会查询包含key:oneid的然后再去判断
select * from json_demo where  address->'$.oneid' <> '1';
select address->'$.oneid' from json_demo;
select address->>'$.oneid' from json_demo;
select JSON_KEYS(address) from json_demo;
	
select * from json_demo where  JSON_CONTAINS(address->'$.oneid' = 1);
		
select * from json_demo WHERE JSON_CONTAINS(payment_method->'$[*].name','"城镇职工基本医疗保险"') and JSON_CONTAINS(payment_method->'$[*].name', '"新型农村合作医疗"');
-- 		可以写=0表示不包含，不写默认为包含  ，要特别注意不能带引号，带的话创建出来的对象会是 {"name",""城镇职工基本医疗保险""}
select * from json_demo WHERE JSON_CONTAINS(payment_method,JSON_OBJECT('name','城镇职工基本医疗保险')) = 0;
-- 		特别注意带引号
select * from json_demo WHERE JSON_CONTAINS(payment_method,'"城镇职工基本医疗保险"') = 0;
-- 		不能用=0或1来表示存在不存在
select * from json_demo WHERE JSON_CONTAINS(payment_method->'$[*].name','"城镇职工基本医疗保险"');
		
select * from json_demo WHERE JSON_CONTAINS_PATH(address,'one','$.oneid');
select * from json_demo WHERE JSON_CONTAINS_PATH(address,'one','$.oneid','$.oneone');
select * from json_demo WHERE JSON_CONTAINS_PATH(address,'all','$.oneid','$.oneone');
select * from json_demo WHERE JSON_CONTAINS_PATH(payment_method,'one','$[*].name','$.oneone');
			
select * from json_demo WHERE '城镇职工基本医疗保险' MEMBER OF (payment_method);

替换没有key的json数组的值--->
one即查询到一个符合条件的路径后就终止
JSON_SEARCH查出来的数组未知是"$[2]"，所以要用JSON_UNQUOTE删除""
 UPDATE health_record SET allergy_json = JSON_REPLACE(allergy_json, JSON_UNQUOTE(JSON_SEARCH(allergy_json, 'one', '原来的值')), '要替换的值') where JSON_CONTAINS(allergy_json, '["原来的值"]');
 
提取出json里的stenosisLeftNeck的value
select id,JSON_EXTRACT(check_json,'$.stenosisLeftNeck') from cz_hcvd_neck_ultrasound
$ . paramsName：取出一个key对应的value。
$ **.paramsName 、$ .[*].paramsName：取出json数组所有该字段key对应的value并以，的方式拼接在一起

行子查询

wk.baidu.com/view/c21ec1…

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)

注：(1,2) 等同于 row(1,2)

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)