Sql where in 多个参，操作单条数据有问题？
2019-03-29 本文已影响0人
南岩飞雪
问题
- 如下写法在queryList的size等于1的时候，会出错
<delete id="batchDeleteByQueryList">
delete from xxx where corp_id = #{corpId} and (user_id, active_time) in
<foreach collection="queryList" item="item" separator="," open="(" close=")">
(#{item.userId}, #{item.activeTime})
</foreach>
</delete>
- 报错
Where in: Operand should contain 2 column(s)
临时解决，大家可以深挖
- 后来发现阿里云tddl分库分表有问题，mysql没问题
- 单条数据特殊处理下
<delete id="deleteByUserIdAndActiveTime">
delete from xxx
where corp_id = #{corpId} and user_id = #{userId} and active_time = #{activeTime}
</delete>
原因
- queryList的size等于1，foreach里拼接结果少了一对括号
