在MySQL中使用IN子句的不同方法 [英] Different approach of using IN clause in MySql

问题描述

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

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)

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

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

expr IN(值，...)

expr IN (value,...)

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

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

它清楚地表明上述(我的查询)是正确的.但以上两个查询都产生相同的输出.

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

为什么还不列出 MySQL文档 ?

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

_{该问题用作有关IN用法的规范信息源.其目的是获得详细，高质量的答案，详细说明查询中IN的正确用法.}

推荐答案

您提出了一个与我的答案有关的问题 这里 .

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 COLUMN that is being compared to multiple values.

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

第二个语句为一个值，与多个列相比 .

while the second statement is A VALUE that is compared to multiple columns.

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

彼此有点不同.

更新1

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

Here's the third form of IN clause:

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

这篇关于在MySQL中使用IN子句的不同方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！