解决Json类型字段的模糊查询:
存储的数据格式:{“type”: “10”, “mobile”: “13545678900”, “countryCode”: “86”}
select * from a where mobile_json->‘$.mobile’ like ‘%135%’;
解决Json类型字段的精确查询
数据存储格式:{“type”: “10”, “mobile”: “13545678900”, “countryCode”: “86”}
select * from a where mobile_json-> ‘$.mobile’ = 13545678900;
解决 JsonArray 类型字段的模糊查询:
存储的数据格式: [{“type”: “10”, “mobile”: “13545678900”, “countryCode”: “86”}]
select * from a where mobile_json->‘$[*].mobile’ like ‘%135%’;
select * from a where JSON_EXTRACT(mobile_json, ‘$[*].mobile’) LIKE ‘%$135%’;
解决 JsonArray 类型字段的精确查询:
存储的数据格式: [{“type”: “10”, “mobile”: “13545678900”, “countryCode”: “86”}]
select * from a where JSON_CONTAINS(mobile_json,JSON_OBJECT(‘mobile’, “13545678900”));