MySQL MySQL中的空IN子句参数列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13210233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Empty IN clause parameter list in MySQL
提问by Wiz
What happens when you do a SQL query where the INclause is empty?
当您在IN子句为空的情况下执行 SQL 查询时会发生什么？
For example:
例如：
SELECT user WHERE id IN ();
Will MySQL handle this as expected (that is, always false), and if not, how can my application handle this case when building the INclause dynamically?
MySQL 是否会按预期处理此问题（即始终为 false），如果没有，我的应用程序在IN动态构建子句时如何处理这种情况？
回答by Barmar
If I have an application where I'm building the INlist dynamically, and it might end up empty, what I sometimes do is initialize the list with an impossible value and add to that. E.g. if it's a list of usernames, I'll start with an empty string, since that's not a possible username. If it's an auto_increment ID, I'll use -1 because the actual values are always positive.
如果我有一个IN动态构建列表的应用程序，它可能最终为空，我有时会用一个不可能的值初始化列表并添加到该列表中。例如，如果它是一个用户名列表，我将从一个空字符串开始，因为这不是一个可能的用户名。如果它是 auto_increment ID，我将使用 -1，因为实际值始终为正。
If this isn't feasible because there are no impossible values, you have to use a conditional to decide whether to include AND column IN ($values)expression in the WHEREclause.
如果这不可行，因为没有不可能的值，则必须使用条件来决定是否AND column IN ($values)在WHERE子句中包含表达式。
回答by Asad Saeeduddin
The closest approximation of this query with valid syntax is:
使用有效语法最接近此查询的近似值是：
SELECT user FROM tbl1 WHERE id IN (SELECT id FROM tbl1 WHERE FALSE);
which unconditionally returns an empty result set. The subquery in the bracket always returns an empty set, and no value can be found in an empty set, since an empty set contains no values.
它无条件地返回一个空的结果集。括号中的子查询总是返回一个空集，并且在空集中找不到任何值，因为空集不包含任何值。
回答by Pavel Shkleinik
This gives me 0 results as well:
这也给了我 0 结果：
SELECT id FROM User
WHERE id IN (NULL);
Tried on MYSQL 5.6
在 MYSQL 5.6 上试过
回答by Jus12
Use an always false statement
使用总是错误的陈述
Before creating the SQL, check for the array size.
If the size is 0, generate the where statement as 1 = 2, as in:
在创建 SQL 之前，请检查数组大小。如果大小为 0，则生成 where 语句 as 1 = 2，如下所示：
SELECT * FROM USERS WHERE name in ()
becomes
变成
SELECT * FROM USERS WHERE 1 = 2
For "not in" on an empty array, generate an always true statement as in:
对于空数组上的“not in”，生成一个始终为真的语句，如下所示：
SELECT * FROM USERS WHERE name not in ()
becomes
变成
SELECT * FROM USERS WHERE 1 = 1
This should work for more complex queries as:
这应该适用于更复杂的查询：
SELECT * FROM USERS WHERE (name in () OR name = 'Alice')
becomes
变成
SELECT * FROM USERS WHERE (1 = 2 OR name = 'Alice')
回答by Manic Depression
If you use AUTO_INCREMENT for id (1,2,3, ..) and if array is empty, you can add one item [0]. So it will be
如果对 id (1,2,3, ..) 使用 AUTO_INCREMENT 并且数组为空，则可以添加一项 [0]。所以它会
if (empty($arr)) {
$arr[] = 0;
}
SELECT user WHERE id IN (0);
And there will be no mysql parse error. This case is very usefull in subqueries - when your main query is not dependent on subquery results.
并且不会出现mysql解析错误。这种情况在子查询中非常有用 - 当您的主查询不依赖于子查询结果时。
Better way - don't call the query if array is empty.
更好的方法 - 如果数组为空，则不要调用查询。
$data = null;
if (!empty($arr)) {
$data = ... call query
}
回答by Vadzim
Here is another variation of always false statement for empty lists that preserves both logic and the notion of actual column in the query.
这是空列表的 always false 语句的另一种变体，它保留了查询中实际列的逻辑和概念。
Incorrect id in ()can be rewritten into:
不正确id in ()可以改写为：
where id <> id;
Similarly incorrect negative condition id not in ()can be transformed by custom query building code into:
同样，不正确的否定条件id not in ()可以通过自定义查询构建代码转换为：
where id = id;
This approach is safer than id not in (NULL)as it doesn't evaluate to NULL.
这种方法比id not in (NULL)不计算为 NULL更安全。
But beware that this would filter out of the result the rows where id is null. This may be considered a feature in some cases.
但请注意，这会过滤掉结果中的行 where id is null。在某些情况下，这可能被视为一个特征。
Espesially useful with complex stacked query building logic where nested builder is not aware how the resulting subquery could be used above.
特别适用于复杂的堆叠查询构建逻辑，其中嵌套构建器不知道如何在上面使用生成的子查询。
回答by Arth
I assume that you still need to run the query when your INis empty; for example with LEFT JOIN ... ON ... AND IN ().
我假设您仍然需要在您IN为空时运行查询；例如与LEFT JOIN ... ON ... AND IN ().
As you are already dynamically building the INin the application layer, I'd handle the empty case there.
由于您已经IN在应用程序层中动态构建了，因此我会在那里处理空情况。
Here's a basic example in PHP
这是 PHP 中的一个基本示例
$condition = 'FALSE' // Could feasibly want TRUE under different circumstances
if($values){
$inParams = **dynamically generated IN parameters from $values**;
$condition = 'IN (' . $inParams . ')';
}
$sql = 'SELECT * FROM `user` WHERE '.$condition;
If you don't need to run the query with an empty IN, then don't; save yourself a trip to the database!
如果您不需要使用空运行查询IN，则不要；为自己节省一次数据库之旅！
N.B.In case you aren't already, I'd build/use a function that goes the long way round and binds in your INparameters properly, rather than just concatting them raw into the SQL. This will give you some protection against SQL injection if it's used on raw data.
注意，如果您还没有，我会构建/使用一个功能强大的函数并IN正确绑定您的参数，而不仅仅是将它们原始连接到 SQL 中。如果它用于原始数据，这将为您提供一些防止 SQL 注入的保护。
回答by Давид Шико
You can't leave IN operator empty, you must put into it something, NULLfor example. But even, in this case, it will be not working as expected, because comparing with NULLalways returns NULL(empty). One possible solution is to add a subselect.
例如，您不能将 IN 运算符留空，您必须将某些内容放入其中NULL。但即使在这种情况下，它也不会按预期工作，因为与NULL总是返回NULL（空）相比。一种可能的解决方案是添加一个子选择。
Example:
例子：
SELECT user_id FROM users;
+-----------+
| user_id |
+-----------+
| 1000 |
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
| 1006 |
| 1007 |
| 1008 |
| 1009 |
| 1010 |
+-----------+
SELECT user_id FROM users WHERE user_id NOT IN ();
ERROR: 1064: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at line 1
SELECT user_id FROM users WHERE user_id NOT IN (NULL);
Empty set (0.0003 sec)
SELECT user_id FROM users WHERE user_id IN (1001, 1002, 1003)
AND user_id NOT IN (SELECT user_id FROM users WHERE user_id IN (NULL));
+---------+
| user_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
+---------+
3 rows in set (0.0004 sec)
You cal a little bit modify (shorten) queries, but I think, they also will a little more slowly.
你稍微修改（缩短）查询，但我认为，它们也会慢一点。
回答by chikebox
If you are using that query in an application and you pass dynamically a list of objects to the query, I should not call to the database to do a select with an impossible value, I should return an empty list without calling to the database query, directly.
如果您在应用程序中使用该查询，并且动态地将对象列表传递给查询，我不应该调用数据库来执行带有不可能值的选择，我应该返回一个空列表而不调用数据库查询，直接地。
Because it has no sense to do a query that you know that is empty before calling it.
因为在调用它之前做一个你知道它是空的查询是没有意义的。
