在 MySql 中使用 IN 子句的不同方法

Different approach of using IN clause in MySql

提问by Yogesh Suthar

Today I have posted an answer with a query like this

今天我发布了一个带有这样查询的答案

SELECT * FROM table_name where column_name IN (val1,val2,...)

Some another user has posted the answer a query like this

另一个用户发布了这样的查询的答案

SELECT * FROM table_name where val1 IN (column_name)

As you can see here the position of the column_name and values are interchanged.

正如您在此处看到的， column_name 和值的位置互换了。

From Mysql Docs

来自 Mysql 文档

expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants.

expr IN（值，...）

如果 expr 等于 IN 列表中的任何值，则返回 1，否则返回 0。如果所有值都是常量，则根据 expr 的类型对它们求值并进行排序。然后使用二分搜索完成对项目的搜索。这意味着如果 IN 值列表完全由常量组成，则 IN 非常快。

mysql> SELECT 2 IN (0,3,5,7);
-> 0

mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1

As it clearly says that the above one(my query) is correct. but both the above queries produce the same output.

正如它清楚地表明上述（我的查询）是正确的。但上述两个查询产生相同的输出。

Also why not the other approach in listed in Mysql Documentation?

另外，为什么不在Mysql 文档中列出的另一种方法？

_{This question serves as a canonical information source regarding the use of IN. Its purpose is to have detailed, high quality answers detailing the proper use on IN in queries.}

_{这个问题是关于 IN 使用的规范信息来源。其目的是提供详细、高质量的答案，详细说明查询中 IN 的正确使用。}

回答by John Woo

you raised a question that is connected with my answer here.

你在这里提出了一个与我的回答有关的问题。

In a simple explanation using this statements below,

在下面使用此语句的简单解释中，

SELECT * FROM TableName WHERE column1 IN (1, 2, 3, 4)
-- versus
SELECT * FROM TableName WHERE 1 IN (column1, column2, column3, column4)

The first statement involves only ONE COLUMNthat is being compared to multiple values.

第一个语句只涉及与多个值进行比较的ONE COLUMN。

SELECT  *
FROM   TableName
WHERE  column1 = 1 OR
       column1 = 2 OR
       column1 = 3 OR
       column1 = 4

while the second statement is A VALUEthat is compared to multiple columns.

而第二条语句是一个值是比较多列。

SELECT  *
FROM   TableName
WHERE  column1 = 1 OR
       column2 = 1 OR
       column3 = 1 OR
       column4 = 1

which is a bit different from one another.

这有点不同。

---

---

UPDATE 1

更新 1

Here's the third form of INclause:

这是IN子句的第三种形式：

• Multi-Column IN clause – Unexpected MySQL Issue

• 多列 IN 子句 – 意外的 MySQL 问题

回答by Yogesh Suthar

As noted in the question, the MySQL documentation gives the form of the IN clause as:

正如问题中所指出的，MySQL 文档给出了 IN 子句的形式：

expr IN (value,...)

expris an expression that can only have a single value for a given row - this value can be a constant (such as 2), the name of a column (such as column_namefrom the question) which will have a specific value on a given row, or any expression including functions, operators, constants and column_names that produces a single value for a given row.

expr是一个表达式，对于给定的行只能有一个值 - 该值可以是一个常量（例如 2），列的名称（例如column_name来自问题的），它将在给定的行上具有特定的值，或任何表达式，包括为给定行生成单个值的函数、运算符、常量和 column_names。

The contents of the parentheses - value,...- contains an expression that can be evaluated as supplying a list of values (potentially including an empty list of values, in which case the incondition would be evaluated as false). This list of values could be:

括号的内容 - value,...- 包含一个表达式，该表达式可以被评估为提供一个值列表（可能包括一个空的值列表，在这种情况下，in条件将被评估为假）。此值列表可以是：

• in the form of an explicit series of one or more constants (such as val1,val2,...) and/or one or more single-valued column names for a given row (such as column_name) and/or a series of expressions that each produce a single value for a given row; or
• in the form of a selectclause that returns a set of values (such as select column_name from table where ...) that can then be compared with the value of expr(immediately preceding the INoperator).

• 在显式串联的一个或多个常数（例如的形式val1,val2,...）和/或一种或多种单值的列名对于给定的行（例如column_name）和/或一系列的表达式，每个产生用于单个值给定的行；或者
• 以select子句的形式返回一组值（例如select column_name from table where ...），然后可以将这些值与expr（紧接在IN运算符之前的）的值进行比较。

To summarise: exprmust evaluate to a single value (for a given row), while the parenthesised set of values to be matched can be evaluated to 0, 1 or more values.

总结：expr必须评估为单个值（对于给定的行），而要匹配的括号中的一组值可以评估为 0、1 或多个值。

In this respect, MySQL operates the same way as any other variant of SQL that I have used.

在这方面，MySQL 的操作方式与我使用过的任何其他 SQL 变体相同。

Where MySQL doesvary is that the entire expr IN (value,...)clause itself evaluates to 0 or 1, which MySQL treats as false or true respectively - in most variants of SQL, the entire clause would evaluate to a boolean FALSE or TRUE.

MySQL在哪儿做改变的是，整个expr IN (value,...)条款本身的计算结果为0或1，其中MySQL的对待分别或真或假-大多数SQL变体，整个子句将生成一个布尔或真或假。