MySQL“in 子句"内的项目数

2021-11-20 00:00:00 sql mysql 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 列表 vs. 临时表

相关文章