SQL 在sql中按ID选择多行的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2379357/
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
What is the best way to select multiple rows by ID in sql?
提问by lashX
I need to select multiple records
我需要选择多条记录
I use
我用
SELECT *
FROM `table`
WHERE `ID` =5623
OR `ID` =5625
OR `ID` =5628
OR `ID` =5621
this query runs 4 times per second with php
此查询每秒使用 php 运行 4 次
is there a better and faster way for this ?
有没有更好更快的方法？
回答by anthares
SELECT *
FROM `table`
where ID in (5263, 5625, 5628, 5621)
is probably better, but not faster.
可能更好，但不会更快。
回答by DVK
On top of what the people stated (where id in):
除了人们所说的（id 所在的位置）之外：
1) iF there are a lot of IDs, better to stick them into a temp table and run a join with that temp table
1) 如果有很多 ID，最好将它们粘贴到临时表中并与该临时表运行连接
2) Make sure your table has an index on id
2) 确保你的表在 id 上有一个索引
3) If the real-timeliness of the data is not critical, put the query results in a cache on the application side
3）如果对数据的实时性要求不高，将查询结果放在应用端的缓存中
回答by Nicholas Murray
SELECT *
FROM `table`
WHERE `ID` in (5623, 5625, 5628, 5621)
While Researching this further I came across an interesting blog postthat explains how to use a set to get faster SQL performance from In clauses, by creating list of ids into a Common Table Expression (CTE) and then joining on that.
在进一步研究这一点时，我发现了一篇有趣的博客文章，它解释了如何使用集合从 In 子句中获得更快的 SQL 性能，方法是将 id 列表创建到公共表表达式 (CTE) 中，然后加入它。
So you could code the PHP equivalent of the following to get maximum performance.
因此，您可以编写与以下等效的 PHP 代码以获得最佳性能。
DECLARE @idList varchar(256)
SET @idList = '5623, 5625, 5628, 5621'
;with ids (id) as
(
SELECT value FROM UTILfn_Split(@idList,',')
)
SELECT t.*
FROM table as t
INNER JOIN ids
ON t.ID = ids.id
回答by Felix Kling
SELECT *
FROM `table`
WHERE `ID` IN (5623, 5625, 5628, 5621)
But four times per second is a lot.I would think about another approach that needs less database access.
但是每秒四次已经很多了。我会考虑另一种需要较少数据库访问的方法。
回答by Marcos Placona
If you do
如果你这样做
SELECT *
FROM `table`
WHERE `ID` IN (5623, 5625, 5628, 5621)
You should be fine, as long as the number of ID's don't get too big. I'd suggest you create an index for the ID's, so the query will perform faster.
你应该没问题，只要 ID 的数量不要太大。我建议您为 ID 创建一个索引，以便查询执行得更快。
Also, consider changing your logic, so you don't have to pass that many ID's
另外，请考虑更改您的逻辑，这样您就不必传递那么多 ID
