解决SQL查询,in条件参数为带逗号的字符串而导致查询结果错误

问题描述:前端页面传参一个数组,而通过SpringMVC框架将数组自动转换了,如页面传参为[1,2,3],框架转换成“1,2,3”这样的字符串,所以我直接在sql中条件中拼接的in查询,写成in ( + 参数值 +)这种,然而sql执行的结果并不是我们想要的结果,如参数中“1,2,3”,他总是只查询出了1,没有查询出2和3。

问题原因:

在MySQL中in里面的如果是字符串的话,会自动转化类型的,可能会转换成整数类型(SIGNED) ,类似内部使用了如下方法:CAST(‘1,2’ AS SIGNED),导致’1,2‘ 变成了1,所以上述查询sql结果只有第一个。

验证如上结论:

我们须知道如果字符串‘1,2’转换成整数会是1:

以我的某张表为例,表中数据如下:

然后执行sql并观察结果,为了验证原因是否正确,我们可以查看sql的执行计划(通过explain extended加上show warnings,我们能够看到在sql真正被执行前优化器做了哪些改写):

可以很清楚地看到我们想要查询出‘1002’和‘1003’两条,但sql只查询出了一条,可以看到show warnings中的信息,对‘1002,1003’这样的字符串进行了优化。

我们可以通过trace分析优化器是如何选择执行计划(注意:仅MySQL5.6及以上版本支持):

首先打开trace,设置格式为JSON,

设置trace最大能够使用的内存大小,避免因为默认内存过小而无法完整显示。

执行sql,并检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道具体执行计划了。

可以看到最终优化后执行的sql如上,将in (‘1002,1003’) 优化成了id=1002 !!

知道了原因,我们就有对应的解决办法,对于这种被‘,’符号分开的自链组成的字符串,MySQL提供了一个很方便的函数FIND_IN_SET(str,strlist),可以达到我们真正想达到的in效果。。

FIND_IN_SET(str,strlist)函数:
假如字符串str 在由N 子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则 FIND_IN_SET() 函数被优化,使用比特计算。如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。返回值为str在strlist中的位置,从1开始计数。

所以最终sql可以这样写:

在这里插入图片描述


转自:https://blog.csdn.net/H_233/article/details/87814533

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值