问题描述:查询出来的json格式的字段值被in()使用的时候,如果你没有对字段值的引号做去除操作,mysql也会对它做处理,但是这样会加长mysql查询的时间,数据量超过万级,耗时就会是很长很长。必须要对json取出来的值做引号剔除。
举个例子:
mysql版本是5.7.x
表名为: student
id | person |
2 | {"name":"wahaha","sex":"nan","birth":"2020-01-01"} |
3 | {"name":"xiaoming","sex":"nan","birth":"2020-01-02"} |
select person->'$.name' from student where id=2;
输出是 "wahaha"
select JSON_EXTRACT(person,'$.name') from student where id=2;
输出是 "wahaha"
解决办法如下:
方法一:
select person->>'$.name' from student where id=2;
输出是 wahaha
方法二:
select JSON_UNQUOTE(JSON_EXTRACT(person,'$.name')) from student where id=2;
输出是 wahaha
建议这样写:
select CAST(JSON_UNQUOTE(JSON_EXTRACT(person,'$.name')) as CHAR) from student where id=2;
输出是 wahaha
因为获取的数据可能需要做比较,所以要把类型统一,CAST(xx as CHAR)表示将数据转为字符类型进行处理;
参考信息来自于:https://blog.csdn.net/songjingzhou/article/details/82870989