在 SQLite WHERE 子句中组合大量条件 [英] Combining a large number of conditions in SQLite WHERE clause

问题描述

我需要检索与列表中存储的 ID 匹配的记录.在运行时生成的查询很简单:

I need to retrieve records that match IDs stored in a list. The query, generated at runtime, is simple:

SELECT [whatever FROM sometable] WHERE (id = 1) or (id = 5) or (id = 33).

相当于

SELECT [whatever FROM sometable] WHERE [id] IN (1, 5, 33);

这很好，但是如果列表包含数百或数千个 ID 呢?该语句将是巨大的，并且在某些时候 SQL 解析器可能会发出嘶嘶声，否则，性能可能会非常糟糕.如何以一种对检索的记录数量不那么敏感的方式执行此操作?

This is fine, but what if the list contains hundreds or thousands of IDs? The statement will be huge and at some point the SQL parser might croak, or if it does not, performance will probably be quite bad. How can I do this in a way that is not so sensitive to the number of records being retrieved?

(我不能只是遍历列表并一条一条检索记录的原因是我需要数据库为我做 ORDER BY.记录必须来自按特定字段排序的数据库，而该列表表示用户在网格中选择的记录，可以以多种方式进行排序.是的，我可以在检索记录后在代码中对记录进行排序，但这是 B 计划，因为我什至不需要持有它们都在一个数据结构中，只是为了正确排序.)

推荐答案

如果你真的有很多 ID 以至于你担心 SQL 解析器发出嘶嘶声，你可以将它们存储到一个临时表中，然后做一个交叉连接.

If you're really going to have so many IDs that you're worried about the SQL parser croaking, you can store them into a temporary table and do a cross-join.

简单地用一个(主键)列创建表，即 ID，然后用所需的 ID 填充它并使用类似的内容:

Simply create the table with one (primary key) column, the ID, then populate it with the desired IDs and use something like:

SELECT [whatever] FROM [sometable] st, [idtable] it
WHERE st.id = it.id

该查询不会阻塞任何解析器，并且检索到的行将仅限于在临时表中具有 ID 的行.

That query won't choke any parser and the rows retrieved will be limited to those having the ID in the temporary table.

这不必成为临时表，当然，您可以将它放在一边，前提是您确保一次只有一个事物"使用它.

This doesn't have to be a temporary table, of course, you can leave it lying around provided you ensure only one "thing" uses it at a time.

这篇关于在 SQLite WHERE 子句中组合大量条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！