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

Question

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

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

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

SELECT * FROM table_name where val1 IN (column_name)

如您所见，column_name 和值的位置已互换。

来自 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.

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

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

因为它清楚地表明上面的(我的查询)是正确的。但上述两个查询产生相同的输出。

另外，为什么不在 Mysql Documentation 中列出其他方法？ ?

_{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.}

Answer 1

你提出了一个与我的回答相关的问题here .

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

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

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

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

而第二条语句是 A VALUE，与多个列进行比较。

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

彼此有点不同。

---

更新 1

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

• Multi-Column IN clause – Unexpected MySQL Issue