如何在WHERE子句中使用ANY而不是IN？

Question

我以前有一个类似于Rails的查询：

MyModel.where(id: ids)

它生成sql查询，如下所示：

SELECT "my_models".* FROM "my_models"
WHERE  "my_models"."id" IN (1, 28, 7, 8, 12)

现在我想将其更改为使用ANY而不是IN。我创建了这个：

MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"

现在，当我使用空数组时，我得到以下错误：

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'

Answer 1

IN表达式有两种变体：

• 表达式IN（子查询）
• 表达式IN（值[，...]）

同样，具有ANY构造的两个变体：

• 表达式运算符ANY（子查询）
• 表达式操作符ANY（数组表达式）

子查询适用于这两种技术，但对于每种技术的第二种形式，中的需要一个值列表（如标准SQL中定义的那样），而=任何都需要一个数组。

ANY是一个更新的、更通用的添加，它可以与任何返回布尔值的二进制运算符结合使用。IN可归结为ANY的特例。事实上，它的第二种形式是在内部重写的：

中的被重写为=任何
不在中的被重写为

检查EXPLAIN（解释）输出中的任何查询，以便自己查看。这证明了两件事：

• IN永远不会比=ANY更快。
• =ANY不会更快。

选择应该由更容易提供的内容决定：值列表或数组（可能是数组文字-单个值）。

如果您要传递的ID无论如何都来自DB内部，那么直接选择它们（子查询）或使用连接将源表集成到查询中（如@mu注释）会更加有效。

要从客户端传递一长串值并获得最佳性能，请使用数组、unnest（）和join，或者使用值将其作为表表达式提供（如@PinnyM注释）。但请注意，联接会在提供的数组/集中保留可能的重复项，而在或=任何中则不会。更多信息：

• 使用大IN优化Postgres查询

在存在空值的情况下，不在通常是错误的选择，而不存在则是正确的（而且速度更快）：

• 选择其他表中不存在的行

对于Postgres接受的数组表达式：

• 数组构造函数（数组由Postgres端的值列表构造），其形式为：数组[1,2,3]
• 或形式为“{1,2,3}”的数组文本

要避免无效的类型转换，可以显式转换：

ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]

相关：

• PostgreSQL：将数组传递给过程的问题
• 如何将自定义类型数组传递给Postgres函数

或者，您可以创建一个Postgres函数，该函数使用一个可变参数，该参数接受各个参数并从中形成一个数组：

• 在单个参数中传递多个值

假设id为整数：

MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})

但我只是在尝试Ruby@mu在相关回答中提供了详细说明：

• 在ruby中向sql查询发送值数组