SQL 限制 WHERE col IN (...) 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1069415/
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
Limit on the WHERE col IN (...) condition
提问by jDeveloper
I'm using the following code:
我正在使用以下代码：
SELECT * FROM table
WHERE Col IN (123,123,222,....)
However, if I put more than ~3000 numbers in the INclause, SQL throws an error.
但是，如果我在IN子句中放入超过 3000 个数字，SQL 会抛出错误。
Does anyone know if there's a size limit or anything similar?!!
有谁知道是否有尺寸限制或类似的东西？！
回答by tekBlues
Depending on the database engine you are using, there can be limits on the length of an instruction.
根据您使用的数据库引擎，指令的长度可能会有限制。
SQL Server has a very large limit:
SQL Server 有一个非常大的限制：
http://msdn.microsoft.com/en-us/library/ms143432.aspx
http://msdn.microsoft.com/en-us/library/ms143432.aspx
ORACLE has a very easy to reach limit on the other side.
ORACLE 有一个很容易达到的限制。
So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.
因此，对于大 IN 子句，最好创建一个临时表，插入值并执行 JOIN。它的工作速度也更快。
回答by Iain Hoult
There is a limit, but you can split your values into separate blocks of in()
有一个限制，但您可以将您的值拆分为单独的 in() 块
Select *
From table
Where Col IN (123,123,222,....)
or Col IN (456,878,888,....)
回答by Greg
Parameterize the query and pass the ids in using a Table Valued Parameter.
参数化查询并使用表值参数传递 id 。
For example, define the following type:
例如，定义以下类型：
CREATE TYPE IdTable AS TABLE (Id INT NOT NULL PRIMARY KEY)
Along with the following stored procedure:
连同以下存储过程：
CREATE PROCEDURE sp__Procedure_Name
@OrderIDs IdTable READONLY,
AS
SELECT *
FROM table
WHERE Col IN (SELECT Id FROM @OrderIDs)
回答by DRapp
Why not do a where IN a sub-select...
为什么不做一个 where IN 子选择...
Pre-query into a temp table or something...
预查询到临时表或其他东西......
CREATE TABLE SomeTempTable AS
SELECT YourColumn
FROM SomeTable
WHERE UserPickedMultipleRecordsFromSomeListOrSomething
then...
然后...
SELECT * FROM OtherTable
WHERE YourColumn IN ( SELECT YourColumn FROM SomeTempTable )
回答by A-K
Depending on your version, use a table valued parameter in 2008, or some approach described here:
根据您的版本，在 2008 年使用表值参数，或此处描述的某些方法：
回答by ENOTTY
You did not specify the database engine in question; in Oracle, an option is to use tuples like this:
您没有指定相关的数据库引擎；在 Oracle 中，一个选项是使用这样的元组：
SELECT * FROM table WHERE (Col, 1) IN ((123,1),(123,1),(222,1),....)
SELECT * FROM table WHERE (Col, 1) IN ((123,1),(123,1),(222,1),....)
This ugly hack only works in Oracle SQL, see https://asktom.oracle.com/pls/asktom/asktom.search?tag=limit-and-conversion-very-long-in-list-where-x-in#9538075800346844400
这个丑陋的黑客只适用于 Oracle SQL，请参阅https://asktom.oracle.com/pls/asktom/asktom.search?tag=limit-and-conversion-very-long-in-list-where-x-in# 9538075800346844400
However, a much better option is to use stored procedures and pass the values as an array.
但是，更好的选择是使用存储过程并将值作为数组传递。
回答by roncansan
For MS SQL 2016, passing ints into the in, it looks like it can handle close to 38,000 records.
对于 MS SQL 2016，将整数传入 in，看起来它可以处理接近 38,000 条记录。
select * from user where userId in (1,2,3,etc)
回答by Lukasz
You can use tuples like this: SELECT * FROM table WHERE (Col, 1) IN ((123,1),(123,1),(222,1),....)
您可以使用这样的元组：SELECT * FROM table WHERE (Col, 1) IN ((123,1),(123,1),(222,1),....)
There are no restrictions on number of these. It compares pairs.
这些数量没有限制。它比较对。
