MySQL“in 子句"内的项目数 [英] MySQL number of items within "in clause"
问题描述
我有三个表来定义用户:
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')
目前我将子查询字符串存储在一个变量中，然后在每次需要提取用户列表时将其动态插入到外部查询中.这样做之后，我想，最好只存储一串实际的 user_ids".
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".
所以不要将其存储在变量中...
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)";
最后是问题:
您可以在 MySQL IN CLAUSE 中使用多少项?每次执行外部查询时，存储实际 id 而不是 sub-sql 语句必须更快，对吗?
How many items can you use in a MySQL IN CLAUSE? Storing the actual ids instead of the sub-sql statement has got to be faster for performing that outer query each time, right?
推荐答案
从一定数量开始，IN 表更快.
Starting from a certain number, the IN tables are faster.
MySQL 在它的代码中有一些东西，这使得在大量常量值上构建一个范围比在嵌套循环中做同样的要慢.
MySQL has 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.
有关性能详细信息，请参阅我博客中的这篇文章:
See this article in my blog for performance details:
这篇关于MySQL“in 子句"内的项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
