postgresql 如何在 Rails 的 WHERE 子句中使用 ANY 而不是 IN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31191507/
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
How to use ANY instead of IN in a WHERE clause with Rails?
提问by Eki Eqbal
I used to have a query like:
我曾经有一个查询，如：
MyModel.where(id: ids)
Which generates sql query like:
它生成 sql 查询，如：
SELECT "my_models".* FROM "my_models"
WHERE "my_models"."id" IN (1, 28, 7, 8, 12)
Now I want to change this to use ANYinstead of IN. I created this:
现在我想将其更改为使用ANY而不是IN. 我创建了这个：
MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"
Now when I use empty array ids = []I get the folowing error:
现在，当我使用空数组时ids = []，出现以下错误：
MyModel Load (53.0ms) SELECT "my_models".* FROM "my_models" WHERE (id = ANY(VALUES()))
ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 75: SELECT "social_messages".* FROM "social_messages" WHERE (id = ANY(VALUES()))
from arjdbc/jdbc/RubyJdbcConnection.java:838:in `execute_query'
回答by Erwin Brandstetter
There are two variants of INexpressions:
IN表达式有两种变体：
Similarly, two variants with the ANYconstruct:
同样，具有ANY构造的两个变体：
A subquery works for either technique, but for the secondform of each, INexpects a list of values(as defined in standard SQL) while = ANYexpects an array.
子查询适用于任何一种技术，但对于每种技术的第二种形式，IN需要一个值列表（如标准 SQL 中所定义），而= ANY需要一个数组。
Which to use?
使用哪个？
ANYis a later, more versatile addition, it can be combined with any binary operator returning a boolean value. INburns down to a special case of ANY. In fact, its second form is rewritten internally:
ANY是后来的，更通用的加法，它可以与任何返回布尔值的二元运算符结合使用。IN燃烧到 的特殊情况ANY。实际上，它的第二种形式是在内部重写的：
INis rewritten with = ANYNOT INis rewritten with <> ALL
IN被重写= ANYNOT IN被重写<> ALL
Check the EXPLAINoutput for any query to see for yourself. This proves two things:
检查EXPLAIN任何查询的输出以自己查看。这证明了两件事：
INcan never be faster than= ANY.= ANYis not going to be substantially faster.
IN永远不会比= ANY.= ANY不会明显更快。
The choice should be decided by what's easier to provide: a list of values or an array (possibly as array literal - a single value).
选择应该由更容易提供的决定：值列表或数组（可能作为数组文字 - 单个值）。
If the IDs you are going to pass come from within the DBanyway, it is much more efficient to select them directly (subquery) or integrate the source table into the query with a JOIN(like @mu commented).
如果您要传递的 ID无论如何都来自数据库，那么直接选择它们（子查询）或将源表集成到带有 a 的查询中JOIN（如@mu 注释）会更有效率。
To pass a long listof values from your client and get the best performance, use an array, unnest()and join, or provide it as table expression using VALUES(like @PinnyM commented). But note that a JOINpreserves possible duplicatesin the provided array / set while INor = ANYdo not. More:
要从您的客户端传递一长串值并获得最佳性能，请使用数组unnest()并连接，或使用VALUES（如@PinnyM 评论）将其作为表表达式提供。但请注意， a在提供的数组/集合中JOIN保留可能的重复项，而IN或= ANY不保留。更多的：
In the presence of NULL values, NOT INis often the wrong choice and NOT EXISTSwould be right (and faster, too):
在存在 NULL 值的情况下，NOT IN通常是错误的选择，NOT EXISTS并且是正确的（而且速度更快）：
Syntax for = ANY
语法为 = ANY
For the array expression Postgres accepts:
对于数组表达式 Postgres 接受：
- an array constructor(array is constructed from a list of values on the Postgres side) of the form:
ARRAY[1,2,3] - or an array literalof the form
'{1,2,3}'.
To avoid invalid type casts, you can cast explicitly:
为了避免无效的类型转换，您可以显式转换：
ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]
Related:
有关的：
Or you couldcreate a Postgres function taking a VARIADICparameter, which takes individual arguments and forms an array from them:
或者您可以创建一个带有VARIADIC参数的 Postgres 函数，该函数采用单个参数并从中形成一个数组：
How to pass the array from Ruby?
如何从Ruby传递数组？
Assuming idto be integer:
假设id是integer：
MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})
But I am just dabbling in Ruby. @mu provides detailed instructions in this related answer:
但我只是涉足 Ruby。@mu 在此相关答案中提供了详细说明：
