WHERE x IN（5）vs WHERE x = 5 ...为什么要使用IN？

Something I've noticed recently is people using IN where it seems kind of inappropriate. I'm feeling like I'm missing a trick - perhaps there are speed gains or something?

我最近注意到的一些事情是使用IN的人似乎不合适。我觉得我错过了一个技巧 - 也许有速度提升或什么?

This query:

SELECT * FROM pages WHERE is_visible IN ($visibility)

Why use that? Why not:

为什么要用它?为什么不:

SELECT * FROM pages WHERE is_visible = $visibility

Sure, if it were this:

当然,如果是这样的话:

SELECT * FROM pages WHERE is_visible 
IN ($visibility,$invisibility,$somethingelse)

Then WHERE x IN (1,2,3) is a better alternative to WHERE x = 1 OR x = 2 OR x = 3, right? But surely this is the only scenario where you should use IN?

那么WHERE x IN(1,2,3)是WHERE x = 1 OR x = 2 OR x = 3的更好的替代品,对吧?但肯定这是你应该使用IN的唯一场景吗?

Anyway thanks for your time - I look forward to your replies!

无论如何,谢谢你的时间 - 我期待你的回复!

9 个解决方案

#1

There is no trick here to boost performance, but I don't think there is any performance hit either. So both ways are OK. I agree with you that using an IN clause with only one parameter looks funny, but it is still easy enough to follow.

这里没有提高性能的技巧,但我认为也没有任何性能影响。所以两种方式都可以。我同意你的看法,使用只有一个参数的IN子句看起来很有趣,但它仍然很容易遵循。

I think this probably happens because a developer thinks it is likely that multiple values are allowed in the future (this way the query wouldn't be changed). Or possibly the developer is used to scenarios where multiple values are possible, and they just wrote it that way out of habit.

我认为这可能是因为开发人员认为将来可能允许多个值(这样查询不会被更改)。或者可能开发人员习惯于可能存在多个值的场景,他们只是习惯性地编写它。

#2

Maybe it is unknown wether $visibility refers to a single value or multiple values? Doesn't quite fit your example, but I have seen such use in other places.

也许它是未知的,$ visibility是指单个值还是多个值?不太适合你的例子,但我在其他地方看到过这种用法。

#3

Then WHERE x IN (1,2,3) is a better alternative to WHERE x = 1 OR x = 2 OR x = 3, right? But surely this is the only scenario where you should use IN?

那么WHERE x IN(1,2,3)是WHERE x = 1 OR x = 2 OR x = 3的更好的替代品,对吧?但肯定这是你应该使用IN的唯一场景吗?

No, you can use IN with a subquery as well.

不,您也可以使用IN和子查询。

... where field in (select field from another_table)

...其中的字段(从another_table中选择字段)

#4

Maybe $visibility is dynamically generated, like this:

也许动态生成$ visibility,如下所示:

$visibility = implode(', ', array_map('intval', array(1, 2, 3)));

Where array(1, 2, 3) may come from untrusted sources.

数组(1,2,3)可能来自不受信任的来源。

#5

I think more to the point in understand what IN does.

我想更多的是了解IN的作用。

If you do

如果你这样做

SELECT * FROM pages WHERE is_visible = $visibility

In the above $visibility would have to be a var so is_visibile = 1, So your SQL collects all where is_visible = 1

在上面的$ visibility中必须是var,所以is_visibile = 1,所以你的SQL收集所有的地方is_visible = 1

Where

SELECT * FROM pages WHERE is_visible IN ($visibility)

The $visibility would be an array of data like @Ionut G. Stan has illustrated.

$ visibility是一系列数据,如@Ionut G. Stan所说明的。

So your call would look like is_visible IN ('1', '2', '3')

所以你的电话看起来像is_visible IN('1','2','3')

So now your SQL will be collecting all 1,2 & 3 rows.

所以现在你的SQL将收集所有1,2和3行。

Hope that makes sense. Its a big difference.

希望有道理。它有很大的不同。

#6

In acts on any kind of set operation, whereas = is on a single value. So you can use in for multiple records on another table, or any other kind of data structure that represents multiple values.

在对任何类型的集合操作的行为中,而=是在单个值上。因此,您可以在另一个表或任何其他类型的表示多个值的数据结构上使用in。

#7

No, it's not a trick. The two statements:

不,这不是一招。这两个陈述:

SELECT * FROM pages WHERE is_visible IN ($visibility)
SELECT * FROM pages WHERE is_visible = $visibility

are nearly equivalent. We observe that the two statements are equivalent in the trivial case, for example, when $visibility is a scalar with a value of 1.

几乎相同。我们观察到这两个语句在平凡的情况下是等价的,例如,当$ visibility是一个值为1的标量时。

But the statements are not equivalent in the non-trivial cases when $visibility contains something else. We can observe a significant difference in behavior of the two forms. Consider what happens with each form when $visibility is a string containing these example values:

但是,当$ visibility包含其他内容时,这些语句在非平凡的情况下并不相同。我们可以观察到两种形式的行为的显着差异。当$ visibility是包含这些示例值的字符串时,请考虑每个表单会发生什么:

    '1,2,3' 
    '1 OR 1=1'
    'select v.val from vals v'

We observe a significant difference in the resultant SQL statements generated from the two forms:

我们观察到从两种形式生成的结果SQL语句的显着差异:

    SELECT * FROM pages WHERE is_visible IN (1,2,3)
    SELECT * FROM pages WHERE is_visible = 1,2,3

    SELECT * FROM pages WHERE is_visible IN (1 OR 1=1 )
    SELECT * FROM pages WHERE is_visible = 1 OR 1=1

A large concern here, with either form of the statement, is the potential for SQL injection. If $visibility is intended to be a scalar value, then using a bind variable in the statement is a more secure approach, since it avoids anyone from sliding 'extra' SQL syntax into the statement. (Of course, using bind variables doesn't prevent all SQL injection, but it is suitable approach to closing one hole. Using a bind variable will also improve scalability, at least on some DBMS platforms such as Oracle.)

对于SQL注入的可能性,这里有一个很大的问题,无论是哪种形式的语句。如果$ visibility要作为标量值,那么在语句中使用绑定变量是一种更安全的方法,因为它可以避免任何人将“额外”SQL语法滑入语句。 (当然,使用绑定变量并不会阻止所有SQL注入,但它是合适的方法来关闭一个洞。使用绑定变量也将提高可伸缩性,至少在某些DBMS平台上如Oracle。)

Consider what happens when we use a bind variable (placeholder), which we know will NOT be interpreted as SQL syntax. We observe that the two statements ARE indeed equivalent:

考虑当我们使用绑定变量(占位符)时会发生什么,我们知道它不会被解释为SQL语法。我们观察到这两个陈述确实是等价的:

    SELECT * FROM pages WHERE is_visible IN ( ? )
    SELECT * FROM pages WHERE is_visible = ?

for any value supplied for the bind variable.

为bind变量提供的任何值。

HTH

#8

"WHERE x IN (1,2,3)" is the same as "WHERE x = 1 OR x = 2 OR x = 3" anyway.

“WHERE x IN(1,2,3)”与“WHERE x = 1 OR x = 2 OR x = 3”相同。

#9

Then WHERE x IN (1,2,3) is a better alternative to WHERE x = 1 OR x = 2 OR x = 3, right? But surely this is the only scenario where you should use IN?

那么WHERE x IN(1,2,3)是WHERE x = 1 OR x = 2 OR x = 3的更好的替代品,对吧?但肯定这是你应该使用IN的唯一场景吗?

IN () and OR are syntactically equivalent. Examining the execution plan for your two suggestions will show this. IN() simply is a more efficient and easy-to-understand notation.

IN()和OR在语法上是等价的。检查两个建议的执行计划将显示这一点。 IN()只是一种更有效且易于理解的符号。

#1

#2