选项1是唯一好的解决方案。

Why?

Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn't immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There's still the readability issue though... Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for "value is one of those in a given list"

我想你说的是SqlServer，但在Oracle上你有一个硬限制，你可以指定多少个IN元素:1000。

另一种方法可能是使用另一个表来包含id值。然后，这个表可以内部连接到table上，以约束返回的行。这样做的主要优点是不需要动态SQL(即使在最好的情况下也很有问题)，而且不需要无限长的IN子句。

您可以截断另一个表，插入大量的行，然后创建一个索引来提高连接性能。它还可以让您将这些行的积累与数据检索分离，从而为您提供更多调优性能的选项。

更新:尽管您可以使用临时表，但我并不是说您必须或应该使用临时表。用于临时数据的永久表是一种常见的解决方案，其优点超出了本文所述。

第一种选择 在临时表中添加id，并添加与主表的内部连接。

CREATE TABLE #temp (column int)
INSERT INTO #temp (column) 
SELECT t.column1 FROM (VALUES (1),(2),(3),...(10000)) AS t(column1)

Ed guinness的建议确实是一个性能助推器，我有一个这样的问题

select * from table where id in (id1,id2.........long list)

我做了什么:

DECLARE @temp table(
            ID  int
            )
insert into @temp 
select * from dbo.fnSplitter('#idlist#')

然后inner将temp与主表连接起来:

select * from table inner join temp on temp.id = table.id

性能得到了极大的提高。

选项1是唯一好的解决方案。

Why?

Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn't immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There's still the readability issue though... Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for "value is one of those in a given list"