MySQL查询条件字段类型不匹配造成的问题

James 2020年11月19日 206次浏览

今天同事遇到个问题,导致很多同事因为这个问题争执不下。

需求:某一张表中某一个字段存入的数据是另外某一张表的ID集合(使用逗号进行分割),需要检索该张表中该字段包含某个ID的记录。

例如:test表有两个字段(id,value),有一条数据({id:1,value:'1,2,3,4'},这里为了方便,使用JSON表示,实质上他是数据库中的一条记录);这个时候我们使用select * from test where value in (1,3)SQL进行查询,发现居然可以被查询出该条数据。

问题:为什么使用IN关键字能将value字段中包含的数据检索出来?

原因:一开始看这个问题的时候,我很诧异,感觉颠覆了我的认知。1,31,2,3,4怎么可能匹配。经过反复查阅资料,发现,其实不光是IN关键字查询有问题,你使用select * from test where value = 1这条SQL语句一样能查询出来。所以,问题其实是出在了表中字段value的数据类型是varchar,而我们使用条件查询时出入的参数是1或者是3,是没有加引号的,不加引号表示int类型,当一个varchar类型和int类型进行匹配时,mysql会做如下事情:

  1. 将字符串转换成浮点数进行比较
  2. 如果字符串是以数字开头,那么就一直截取,知道不是数字为止;如果字符串是以字符串开头,那么就直接置为0进行比较。
    在看上面的例子,例子中原始数据为1,2,3,4,传入的条件参数为1,3,并且这里使用IN时,会将1,3解析成两个值,所以,在比较时,原始数据被解析成1,条件数据被解析成1,所以才会出现以上错误的结果。

总结:当字段为int类型时,我们可以使用字符串类型的参数进行匹配;当字段为varchar类型时,切记一点不要使用int类型的参数进行检索。当然啦,为了规范,是什么类型就尽量按照匹配的类型进行查询