使用'in'运算符的MySQL查询:为什么用引号引起不同的结果? [英] MySQL query using 'in' operator: why different results w/ quotes?

问题描述

我已经将奇怪的MySQL问题归结为执行查询的两种不同方式.当您煮沸所有东西时，这种方式将返回更多结果:

I've tracked down a weird MySQL problem to the two different ways I was performing a query. When you boil everything down, this way returns more results:

SELECT DISTINCT <stuff> FROM <tables> 
WHERE promo_detail_store_id in (8214, 8217, 4952, 8194, ...)

对WHERE子句的更改产生了这些结果的子集:

This change to the WHERE clause produces a subset of those results:

WHERE promo_detail_store_id in ('8214, 8217, 4952, 8194, ...')

(promo_detail_store_id在MyISAM表中定义为BIGINT.)

(promo_detail_store_id is defined as a BIGINT in a MyISAM table.)

最初，store_id的列表长得多，我开始将其剪短，以为字符串的长度可能存在一些怪异的限制.但是不，它也适用于很小的字符串/列表.显然，涉及类型强制的幕后正在发生某些事情，也许还有"in"运算符的工作方式.有人可以启发我吗?

Originally that list of store_ids was much longer, and I started cutting it shorter and shorter thinking maybe there was some weird limits on the length of a string. But no, it holds for quite small strings/lists too. Clearly something is going on behind the scenes involving type coercion and maybe how the 'in' operator works. Can someone enlighten me?

推荐答案

WHERE promo_detail_store_id in (8214, 8217, 4952, 8194, ...)

表示

WHERE promo_detail_store_id = 8214 
OR  promo_detail_store_id = 8217
OR promo_detail_store_id = 4952 
OR promo_detail_store_id = 8194
OR ... 

---

WHERE promo_detail_store_id in ('8214, 8217, 4952, 8194, ...')

表示

 WHERE promo_detail_store_id = '8214, 8217, 4952, 8194, ...'

'8214, 8217, 4952, 8194, ...'将强制转换为数字8214，因此它将为

'8214, 8217, 4952, 8194, ...' will cast to number to be 8214, so it will be

WHERE promo_detail_store_id = 8214

这篇关于使用'in'运算符的MySQL查询:为什么用引号引起不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！