MySQL“in 子句”中的项目数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1532366/
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
MySQL number of items within "in clause"
提问by Bart
I have three tables to define users:
我有三个表来定义用户：
USER: user_id (int), username (varchar)
USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar)
USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar)
I'd like to create a middle tier user that has certain access to other users within the application. To determine which users the logged in use can access, I am using a subquery like the following:
我想创建一个中间层用户，该用户对应用程序中的其他用户具有一定的访问权限。为了确定登录用户可以访问哪些用户，我使用了如下子查询：
SELECT user_id FROM user WHERE user_id
IN (SELECT user_id
FROM user_metadata
WHERE user_metadata_field_id = 1 AND field_value = 'foo')
Currently I am storing the subquery string in a variable and then dynamically inserting it into the outer query each time I need to pull a list of users. After doing this I thought, "it has got to be better to just store a string of the actual user_ids".
目前，我将子查询字符串存储在一个变量中，然后在每次需要提取用户列表时将其动态插入到外部查询中。这样做之后，我想，“最好只存储一串实际的user_ids”。
So instead of storing this in a variable...
因此，与其将其存储在变量中...
$subSql = "SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = 'foo'";
... I actually perform the query and store the result like this...
...我实际上执行查询并像这样存储结果......
$subSql = "12, 56, 89, 100, 1234, 890";
Then when I need to pull a lit of users that the logged in user has access to, I can do so with:
然后，当我需要拉一些登录用户有权访问的用户时，我可以这样做：
$sql = "SELECT user_id FROM user WHERE user_id IN ($subSql)";
And finally the questions:
最后是问题：
How many items can you use in a MySQL INCLAUSE? Storing the actual ids instead of the sub-sql statement has got to be faster for performing that outer query each time, right?
您可以在 MySQL INCLAUSE 中使用多少项？每次执行外部查询时，存储实际 id 而不是 sub-sql 语句必须更快，对吗？
采纳答案by Quassnoi
Starting from a certain number, the INtables are faster.
从某个数字开始，IN表格会更快。
MySQLhas something inside its code that makes building a range over a large number of constant values slower than doing the same in a nested loop.
MySQL在它的代码中有一些东西使得在大量常量值上构建一个范围比在嵌套循环中做同样的要慢。
See this article in my blog for performance details:
有关性能详细信息，请参阅我博客中的这篇文章：
回答by RedFilter
From the manual:
从手册：
The number of values in the INlist is only limited by the max_allowed_packetvalue.
IN列表中值的数量仅受该max_allowed_packet值的限制。
回答by mjv
As hinted in Quassnoi's response, one stumbles upon other practical considerations, beforehitting any possible limitimposed by a given MySql version's implementation (*). Therefore, as the number of admin users (or other criteria which may require an IN construct) grows, one should seek to use alternatives to a literal "IN", such as the use of temporary (or even permanent) tables.
正如 Quassnoi 的回应所暗示的那样，在遇到给定 MySql 版本的实现 (*) 施加的任何可能限制之前，人们会偶然发现其他实际考虑因素。因此，随着管理员用户（或其他可能需要 IN 构造的标准）数量的增长，人们应该寻求使用文字“IN”的替代方案，例如使用临时（甚至永久）表。
Since you are considering special handling of the "admin user" criteria, for performance purposes, I'd like to offer an comment and a suggestion.
由于您正在考虑对“管理员用户”标准进行特殊处理，出于性能目的，我想提供评论和建议。
Comment:Could this be a case of premature optimization?
I'm unaware of the specifics of this database, its volume, complexity etc. And, yes, I am aware of some the performance tribute to be paid to the EAV (Entity-Attribute-Value) format, but I'm thinking that even for successful businesses, the accounts database rarely counts in excess of 10,000 users. So even with very many attributes per user we're still looking at a relatively small EAV table, which may not require this type of optimization. (On the other hand a few other optimization tricks may be welcome in other areas).
Furthermore, typical use cases, involve a relative few inquiries into the account database, relative to other queries, and this is therefore another reason to deffer any non trivial performance consideration for the accounts-related features of the application.
评论：这可能是过早优化的情况吗？
我不知道这个数据库的细节，它的数量，复杂性等。而且，是的，我知道一些要向 EAV（实体-属性-值）格式致敬的性能，但我在想即使对于成功的企业，帐户数据库的用户数也很少超过 10,000。因此，即使每个用户有很多属性，我们仍然在查看一个相对较小的 EAV 表，它可能不需要这种类型的优化。（另一方面，在其他领域可能会欢迎一些其他优化技巧）。
此外，与其他查询相比，典型用例涉及对帐户数据库的相对较少的查询，因此这是推迟对应用程序的帐户相关功能进行任何非平凡性能考虑的另一个原因。
Suggestion:Maybe use "re-normalized attributes"
For attributes that are singled-valued, and in particular if they are short, they can be moved (or duplicated) in the Entity table ('USER' table in this case). This introduces a bit of logic at the time items are inserted or updated, but this sames many joins (or subqueries) and also provides opportunities to consider multi-field indexes to support the most common use cases.
建议：也许使用“重新规范化的属性”
对于单值的属性，特别是如果它们很短，它们可以在实体表（在这种情况下为'USER'表）中移动（或复制）。这在插入或更新项目时引入了一些逻辑，但这与许多连接（或子查询）相同，并且还提供了考虑多字段索引以支持最常见用例的机会。
(*) Is there a limt?
I haven't read about any such a limit; I know Oracle has (had) a 1,000 limit at some time, MSSQL doesn't; of course all servers do have a limit based on the overall length of the SQL statement, but this is a really big number! if one ever stumble upon that one, he/she has other problems... ;-)
(*) 有限制吗？
我还没有读到任何这样的限制；我知道 Oracle 在某个时候（有）1,000 个限制，而 MSSQL 没有；当然，所有服务器都有基于 SQL 语句总长度的限制，但这确实是一个很大的数字！如果有人偶然发现那个，他/她还有其他问题...... ;-)
回答by Hidayat
MySQL's IN Clause itself doesn't have such limit. I tried with 8000 elements its work fine for me. Stack overflow error could be of variable declared,
MySQL 的 IN 子句本身没有这样的限制。我尝试了 8000 个元素，它对我来说很好用。堆栈溢出错误可能是声明了变量，
回答by solitud
If you have more than 1000 values within the IN()clause MariaDB seems to automatically create temporary tables for performance improvement. You can see this using EXPLAIN.
如果IN()子句中有超过 1000 个值，MariaDB 似乎会自动创建临时表以提高性能。您可以使用EXPLAIN.
