在MySQL中传递给IN()的项的最大数量是多少?

I am given a bunch of IDs (from an external source) that I need to cross-reference with the ones on our database and filter out those that fall between a certain date and are also "enabled" and some other parameters. I can easily do this by doing:

我得到了一堆id(来自外部源)，我需要与数据库中的id进行交叉引用，并过滤掉那些介于特定日期和“启用”以及其他一些参数之间的id。我很容易做到:

SELECT * FROM `table` WHERE `id` IN (csv_list_of_external_ids) 
    AND (my other cross-reference parameters);

And by doing this, of all those incoming IDs I will get the ones that I want. But obviously this is not a very efficient method when the external ids are in the thousands. And I'm not sure if MySQL will even support such a huge query.

通过这样做，所有输入的id中我将得到我想要的。但显然，当外部id为千的时候，这不是一个非常有效的方法。我不确定MySQL是否会支持这么大的查询。

Given that nothing can be cached (since both the user data and the external ids change pretty much on every query), and that these queries happen at least every 10 seconds. What other SQL alternatives are there?

由于没有任何东西可以缓存(因为用户数据和外部id在每个查询上都有很大的变化)，并且这些查询至少每10秒钟发生一次。还有其他SQL替代方案吗?

2 个解决方案

#1

I believe the only limit is the length of the actual query, which is controlled by the "max_allowed_packet" parameter in your my.cnf file.

我认为唯一的限制是实际查询的长度，它由my.cnf文件中的“max_allowed_packet”参数控制。

#2

If you express it as a subquery:

如果您将其表示为子查询:

SELECT * FROM table
WHERE id IN (SELECT ID FROM SOME_OTHER_TABLE)
AND ...

there is no limit.

没有限制。

#1