blocks|key|4145766|text|如果我有一个动态构建IN列表的应用程序，它可能最终是空的，我有时会用一个不可能的值初始化列表，然后添加到该列表中。例如，如果它是一个用户名列表，我将从一个空字符串开始，因为这不是一个可能的用户名。如果它是一个auto_increment+ID，我将使用-1，因为实际值总是正数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4145767|如果这不可行，因为没有不可能的值，则必须使用条件来决定是否在WHERE子句中包含AND+column+IN+($values)表达式。|4145768|entityMap^0|A|2|0|U|5|14|N|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

If I have an application where I'm building the <code>IN</code> list 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.

If this isn't feasible because there are no impossible values, you have to use a conditional to decide whether to include <code>AND column IN ($values)</code> expression in the <code>WHERE</code> clause.

blocks|key|2035787|text|这也给了我0个结果：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2035788|SELECT+id+FROM+User
WHERE+id+IN+(NULL);|code-block|syntax|javascript|2035789|已在MYSQL+5.6上试用|2035790|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This gives me 0 results as well:

<pre><code>SELECT id FROM User
WHERE id IN (NULL);
</code></pre>

Tried on MYSQL 5.6

blocks|key|2035701|text|使用有效语法的此查询的最接近值为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2035702|SELECT+user+FROM+tbl1+WHERE+id+IN+(SELECT+id+FROM+tbl1+WHERE+FALSE);|code-block|syntax|javascript|2035703|它无条件地返回一个空的结果集。括号中的子查询始终返回空集，并且在空集中找不到任何值，因为空集不包含任何值。|2035704|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The closest approximation of this query with valid syntax is:

<pre><code>SELECT user FROM tbl1 WHERE id IN (SELECT id FROM tbl1 WHERE FALSE);
</code></pre>

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.

blocks|key|2035821|text|使用总是错误的语句|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2035822|在创建SQL之前，请检查数组大小。如果大小为0，则将where语句生成为1+=+2，如下所示：|offset|length|style|CODE|2035823|SELECT+*+FROM+USERS+WHERE+name+in+()+|code-block|syntax|javascript|2035824|变成了|2035825|SELECT+*+FROM+USERS+WHERE+1+=+2+|2035826|对于空数组上的"not+in“，生成一个始终为真的语句，如下所示：|2035827|SELECT+*+FROM+USERS+WHERE+name+not+in+()+|2035828|2035829|SELECT+*+FROM+USERS+WHERE+1+=+1|2035830|这应该适用于更复杂的查询，如下所示：|2035831|SELECT+*+FROM+USERS+WHERE+(name+in+()+OR+name+=+'Alice')+|2035832|2035833|SELECT+*+FROM+USERS+WHERE+(1+=+2+OR+name+=+'Alice')+|2035834|entityMap^0|0|10|5|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1A|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1C|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|A|$]]|$1|S|3|T|5|J|7|1E|8|@]|9|@]|A|$K|L]]|$1|U|3|N|5|6|7|1F|8|@]|9|@]|A|$]]|$1|V|3|W|5|J|7|1G|8|@]|9|@]|A|$K|L]]|$1|X|3|Y|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Z|3|10|5|J|7|1I|8|@]|9|@]|A|$K|L]]|$1|11|3|N|5|6|7|1J|8|@]|9|@]|A|$]]|$1|12|3|13|5|J|7|1K|8|@]|9|@]|A|$K|L]]|$1|14|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|15|$]]

Use an always false statement

Before creating the SQL, check for the array size. 
If the size is 0, generate the where statement as <code>1 = 2</code>, as in:

<pre><code>SELECT * FROM USERS WHERE name in () 
</code></pre>

becomes

<pre><code>SELECT * FROM USERS WHERE 1 = 2 
</code></pre>

For "not in" on an empty array, generate an always true statement as in:

<pre><code>SELECT * FROM USERS WHERE name not in () 
</code></pre>

becomes

<pre><code>SELECT * FROM USERS WHERE 1 = 1
</code></pre>

This should work for more complex queries as:

<pre><code>SELECT * FROM USERS WHERE (name in () OR name = 'Alice') 
</code></pre>

becomes

<pre><code>SELECT * FROM USERS WHERE (1 = 2 OR name = 'Alice') 
</code></pre>

blocks|key|1638002|text|如果您使用AUTO_INCREMENT作为id+(1,2,3，..)如果数组为空，则可以添加一项。所以它将会是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1638003|if+(empty($arr))+{
+++$arr[]+=+0;
}

SELECT+user+WHERE+id+IN+(0);|code-block|syntax|javascript|1638004|并且不会出现mysql解析错误。这种情况在子查询中非常有用--当主查询不依赖于子查询结果时。|1638005|1638006|更好的方法-如果数组为空，不要调用查询。|1638007|$data+=+null;
if+(!empty($arr))+{
++++$data+=+...+call+query
}|1638008|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Q|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|R|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|T|8|@]|9|@]|A|$]]|$1|L|3|M|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|N|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|O|$]]

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

<pre><code>if (empty($arr)) {
 $arr[] = 0;
}

SELECT user WHERE id IN (0);
</code></pre>

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.

<hr>

Better way - don't call the query if array is empty.

<pre><code>$data = null;
if (!empty($arr)) {
 $data = ... call query
}
</code></pre>

blocks|key|5563636|text|你不能让IN操作符为空，你必须在里面放一些东西，例如NULL。但是，即使在这种情况下，它也不会像预期的那样工作，因为与NULL进行比较总是返回NULL+(空)。一种可能的解决方案是添加子选择。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5563637|示例：|5563638|SELECT+user_id+FROM+users;

%2B-----------%2B
%7C+user_id+++%7C
%2B-----------%2B
%7C++++++1000+%7C
%7C++++++1001+%7C
%7C++++++1002+%7C
%7C++++++1003+%7C
%7C++++++1004+%7C
%7C++++++1005+%7C
%7C++++++1006+%7C
%7C++++++1007+%7C
%7C++++++1008+%7C
%7C++++++1009+%7C
%7C++++++1010+%7C
%2B-----------%2B

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));
%2B---------%2B
%7C+user_id+%7C
%2B---------%2B
%7C++++1001+%7C
%7C++++1002+%7C
%7C++++1003+%7C
%2B---------%2B
3+rows+in+set+(0.0004+sec)|code-block|syntax|javascript|5563639|您可以稍微修改(缩短)查询，但我认为，它们也会更慢一些。|5563640|entityMap^0|Q|4|1N|4|1Z|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

You can't leave IN operator empty, you must put into it something, <code>NULL</code> for example. But even, in this case, it will be not working as expected, because comparing with <code>NULL</code> always returns <code>NULL</code> (empty). One possible solution is to add a subselect.

Example:

<pre><code>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)
</code></pre>

You cal a little bit modify (shorten) queries, but I think, they also will a little more slowly.

blocks|key|4145955|text|下面是空列表的always+false语句的另一个变体，它保留了查询中的逻辑和实际列的概念。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4145956|错误的id+in+()可以重写为：|offset|length|style|CODE|4145957|where+id+<>+id;|code-block|syntax|javascript|4145958|同样，不正确的否定条件id+not+in+()可以通过自定义查询构建代码转换为：|4145959|where+id+=+id;|4145960|这种方法比id+not+in+(NULL)更安全，因为它不会求值为NULL。|4145961|但要注意，这会过滤掉结果中的id+is+null行。在某些情况下，这可能被认为是一种特性。|4145962|特别适用于复杂的堆叠查询构建逻辑，其中嵌套构建器不知道如何在上面使用生成子查询。|4145963|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/37419910/603516^0|0|3|8|0|0|B|C|0|0|5|G|5|G|0|0|E|A|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|18|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|19|8|@$D|1A|E|1B|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|1C|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1D|8|@$D|1E|E|1F|F|G]]|9|@$D|1G|E|1H|1|1I]]|A|$]]|$1|S|3|T|5|6|7|1J|8|@$D|1K|E|1L|F|G]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1M|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

Here is another variation of always false statement for empty lists that preserves both logic and the notion of actual column in the query.

Incorrect <code>id in ()</code> can be rewritten into:

<pre><code>where id &lt;&gt; id;
</code></pre>

Similarly incorrect negative condition <code>id not in ()</code> can be transformed by custom query building code into:

<pre><code>where id = id;
</code></pre>

This approach is safer than <a href="https://stackoverflow.com/a/37419910/603516"><code>id not in (NULL)</code></a> as it doesn't evaluate to NULL.

But beware that this would filter out of the result the rows where <code>id is null</code>. This may be considered a feature in some cases.

Espesially useful with complex stacked query building logic where nested builder is not aware how the resulting subquery could be used above.

blocks|key|4145789|text|我假设您仍然需要在IN为空时运行查询；例如，使用LEFT+JOIN+...+ON+...+AND+IN+()。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4145790|由于您已经在应用程序层动态构建了IN，因此我将在那里处理空的情况。|4145791|下面是一个用PHP编写的基本示例|4145792|$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;|code-block|syntax|javascript|4145793|如果您不需要使用空的IN运行查询，那么就不要这样做；这样可以省去访问数据库的麻烦！|4145794|n.b.sql如果您还没有这样做，我会构建/使用一个函数，它可以绕过很长一段时间，正确地绑定您的IN参数，而不仅仅是将它们原始连接到中。如果在原始数据上使用SQL注入，这将为您提供一些保护。|BOLD|4145795|entityMap^0|9|2|O|U|0|G|2|0|0|0|A|2|0|0|1U|1C|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|13|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|14|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|18|8|@$9|19|A|1A|B|S]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|U|$]]

I assume that you still need to run the query when your <code>IN</code> is empty; for example with <code>LEFT JOIN ... ON ... AND IN ()</code>.

As you are already dynamically building the <code>IN</code> in the application layer, I'd handle the empty case there. 

Here's a basic example in PHP

<pre><code>$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;
</code></pre>

If you don't need to run the query with an empty <code>IN</code>, then don't; save yourself a trip to the database!

N.B. In case you aren't already, I'd build/use a function that goes the long way round and binds in your <code>IN</code> parameters 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.

blocks|key|2036025|text|处理这个问题的一种方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2036026|SELECT+user+WHERE+id+in+(SELECT+1+WHERE+1!=1)|offset|length|style|CODE|2036027|可能需要用@JamesHoux的评论中提到的适当数据类型的值替换1。|2036028|这里的假设是用户的id是一个int。|2036029|entityMap^0|0|0|19|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

One way to handle this:
<code>SELECT user WHERE id in (SELECT 1 WHERE 1!=1)</code>
There could be a need to replace 1 with a value of the appropriate data type as mentioned in a comment by @JamesHoux. 
An assumption made here is that a user's id is an int.

blocks|key|4145989|text|如果您在应用程序中使用该查询，且动态地将对象列表传递给该查询，则我不应该调用数据库来使用不可能值进行select，我应该返回一个空列表，而不直接调用数据库查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4145990|因为在调用它之前，做一个你知道是空的查询是没有意义的。|4145991|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

What happens when you do a SQL query where the <code>IN</code> clause is empty?

For example:

<pre><code>SELECT user WHERE id IN ();
</code></pre>

Will MySQL handle this as expected (that is, always false), and if not, how can my application handle this case when building the <code>IN</code> clause dynamically?

Empty IN clause parameter list in MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

在执行IN子句为空的SQL查询时，会发生什么情况？例如：SELECT user WHERE id IN ();MySQL是否会按预期处理(即始终为false)，如果不是，在动态构建IN子句时，应用程序如何处理这种情况？

问MySQL中的空IN子句参数列表
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中的空IN子句参数列表EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中的空IN子句参数列表
EN