SQL WHERE ID IN (id1, id2, ..., idn) [英] SQL WHERE ID IN (id1, id2, ..., idn)
问题描述
我需要编写一个查询来检索一个大的 id 列表.
I need to write a query to retrieve a big list of ids.
我们确实支持许多后端(MySQL、Firebird、SQLServer、Oracle、PostgreSQL ...),所以我需要编写一个标准的 SQL.
We do support many backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL ...) so I need to write a standard SQL.
id 集的大小可能很大,查询将以编程方式生成.那么,最好的方法是什么?
The size of the id set could be big, the query would be generated programmatically. So, what is the best approach?
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
我的问题是.如果 n 很大会发生什么?另外,性能怎么样?
My question here is. What happens if n is very big? Also, what about performance?
SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn
我认为这种方法没有 n 限制,但如果 n 很大,性能如何?
I think that this approach does not have n limit, but what about performance if n is very big?
foreach (var id in myIdList)
{
var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
myObjectList.Add(item);
}
当通过网络查询数据库服务器时,我们遇到了这种方法的一些问题.通常,执行一次检索所有结果的查询比进行大量小查询更好.也许我错了.
We experienced some problems with this approach when the database server is queried over the network. Normally is better to do one query that retrieve all results versus making a lot of small queries. Maybe I'm wrong.
这个问题的正确解决方案是什么?
What would be a correct solution for this problem?
推荐答案
选项 1 是唯一好的解决方案.
Option 1 is the only good solution.
选项 2 的作用相同,但您多次重复列名;此外,SQL 引擎不会立即知道您要检查该值是否是固定列表中的值之一.然而,一个好的 SQL 引擎可以优化它以获得与
IN
相同的性能.仍然存在可读性问题...
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...
选项 3 在性能方面简直太糟糕了.它在每个循环中发送一个查询,并用小查询来冲击数据库.它还可以防止它对值是给定列表中的值之一"使用任何优化
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"
这篇关于SQL WHERE ID IN (id1, id2, ..., idn)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!