MySQL中IN和NOT IN的大小限制是多少？

I get out of memory exception in my application, when the condition for IN or NOT IN is very large. I would like to know what is the limitation for that.

当IN或NOT IN的条件非常大时，我的应用程序出现内存异常。我想知道这有什么限制。

6 个解决方案

#1

Perhaps you would be better off with another way to accomplish your query?

也许你会用另一种方式来完成你的查询会更好吗？

I suggest you load your match values into a single-column table, and then inner-join the column being queried to the single column in the new table.

我建议您将匹配值加载到单列表中，然后将要查询的列内部连接到新表中的单个列。

Rather than

而不是

SELECT a, b, c FROM t1 WHERE d in (d1, d2, d3, d4, ...)

build a temp table with 1 column, call it "dval"

用1列构建临时表，称之为“dval”

dval  
----  
 d1  
 d2  
 d3

SELECT a, b, c FROM t1  
INNER JOIN temptbl ON t1.d = temptbl.dval

#2

Having to ask about limits when either doing a SQL query or database design is a good indicator that you're doing it wrong.

在进行SQL查询或数据库设计时，不得不询问限制，这是一个很好的指标，表明你做错了。

#3

I only ever use IN and NOT IN when the condition is very small (under 100 rows or so). It performs well in those scenarios. I use an OUTER JOIN when the condition is large as the query doesn't have to look up the "IN" condition for every tuple. You just have to check the table that you want all rows to come from.

当条件非常小（大约100行左右）时，我只使用IN和NOT IN。它在这些场景中表现良好。当条件很大时我使用OUTER JOIN，因为查询不必为每个元组查找“IN”条件。您只需要检查您希望所有行来自哪个表。

For "IN" the join condition IS NOT NULL

对于“IN”，连接条件不是NULL

For "NOT IN" the join condition IS NULL

对于“NOT IN”，连接条件为NULL

e.g.

例如

/* Get purchase orders that have never been rejected */
SELECT po.*
FROM PurchaseOrder po LEFT OUTER JOIN 
     (/* Get po's that have been rejected */
     SELECT po.PurchaesOrderID
     FROM PurchaseOrder po INNER JOIN 
         PurchaseOrderStatus pos ON po.PurchaseOrderID = pos.PurchaseOrderID
     WHERE pos.Status = 'REJECTED'
     ) por ON po.PurchaseOrderID = por.PurchaseOrderID
WHERE por.PurchaseOrderID IS NULL    /* We want NOT IN */

#4

I"m having a similar issue but only passing 100 3 digit ids in my IN clause. When I look at the stack trace, it actually cuts off the comma separate values in the IN clause. I don't get an error, I just don't get all the results to return. Has anyone had an issue like this before? If its relevant, I'm using the symfony framework... I'm checking to see if its a propel issue but just wanted to see if it could be sql

我有一个类似的问题，但只在我的IN子句中传递了100个3位数字。当我查看堆栈跟踪时，它实际上切断了IN子句中的逗号分隔值。我没有得到错误，我只是没有得到所有结果返回。有没有人之前有这样的问题？如果相关，我正在使用symfony框架...我正在检查它是否是一个推进问题，但只是想看看是否它可能是sql

#5

I have used IN with quite large lists of IDs - I suspect that the memory problem is not in the query itself. How are you retrieving the results?

我使用了相当大的ID列表 - 我怀疑内存问题不在查询本身。你是如何检索结果的？

This query, for example is from a live site:

例如，此查询来自实时网站：

SELECT DISTINCT c.id, c.name FROM categories c 
LEFT JOIN product_categories pc ON c.id = pc.category_id 
LEFT JOIN products p ON p.id = pc.product_id 
WHERE p.location_id IN (
955,891,901,877,736,918,900,836,846,914,771,773,833,
893,782,742,860,849,850,812,945,775,784,746,1036,863,
750,763,871,817,749,838,986,794,867,758,923,804,733,
949,808,837,741,747,954,939,865,857,787,820,783,760,
911,745,928,818,887,847,978,852
) ORDER BY c.name ASC

My first pass at the code is terribly naive and there are about 10 of these queries on a single page and the database doesn't blink.

我在代码中的第一次传递是非常天真的，并且在单个页面上有大约10个这样的查询，并且数据库不会闪烁。

You could, of course, be running a list of 100k values which would be a different story altogether.

当然，你可以运行一个100k值的列表，这将完全是一个不同的故事。

#6

I don't know what the limit is, but I've run into this problem before as well. I had to rewrite my query something like this:

我不知道限制是什么，但我之前也遇到过这个问题。我不得不重写我的查询，如下所示：

select * from foo
  where id in (select distinct foo_id from bar where ...)

#1