如何在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)

现在我想要将这个使用IN改为使用ANY。我创建了这个：

现在我希望使用ANY代替IN，我创建了以下代码：

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

现在当我使用空数组 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'

- Eki Eqbal

---

如果您要编写自定义查询，请非常小心地使用占位符：VALUES(?)，并根据需要扩展绑定数组比您在此处拥有的更好。您需要小心地正确转义任何注入到SQL中的原始值。这个查询的目的是什么？ - tadman

在PostgreSQL中，“IN”是“= ANY”的别名。 - PinnyM

所有这些ID是从哪里来的？也许你应该使用JOIN或子查询，而不是将一个大的ID列表发送到数据库。 - mu is too short

@PinnyM：IN不是 = ANY的别名。我提供了详细信息。 - Erwin Brandstetter

@PinnyM：实际上，IN 在内部被重写为 = ANY。我在我的答案中添加了详细信息。 - Erwin Brandstetter

Answer 1

有两种IN表达式变体：

• expression IN (subquery)
• expression IN (value [, ...])

同样，使用ANY构造的两个变体：

• expression operator ANY (subquery)
• expression operator ANY (array expression)

子查询适用于任何技术，但对于每个形式的第二种，IN期望一个值列表（如标准SQL中定义的），而= ANY则期望一个数组。

应该使用哪个？

ANY是一个更加灵活的后来的添加，它可以与返回布尔值的任何二进制运算符组合使用。IN可以减少到ANY的特殊情况。实际上，其第二种形式在内部被重写：

IN被重写为= ANY
NOT IN被重写为<> ALL

检查任何查询的EXPLAIN输出以自行验证。这证明了两件事：

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

选择应该由哪个更容易提供来决定：值列表还是数组（可能作为数组文字-单个值）。

如果您要传递的ID来自于DB，那么直接选择它们（子查询）或使用JOIN将源表集成到查询中（如@mu commented）要高效得多。

为了从客户端传递一个 长列表 的值并获得最佳的 性能，请使用一个数组，unnest() 和 join，或者将其作为表达式提供使用 VALUES（例如 @PinnyM commented）。但是请注意，JOIN 会保留提供的数组/集合中可能存在的重复项，而 IN 或 = ANY 则不会。更多信息：

• 优化包含大量 IN 的 Postgres 查询

在存在 NULL 值的情况下，NOT IN 经常是错误的选择，NOT EXISTS 才是正确的选择（并且速度更快）：

• 选择另一个表中不存在的行

= ANY 的语法

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

• 数组构造器（数组从 Postgres 端的值列表构造）的形式为：ARRAY[1,2,3]
• 或者一个 数组字面值 的形式为 '{1,2,3}'

为了避免无效的类型转换，您可以显式地进行强制转换：

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

相关：

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

或者您可以创建一个接受 VARIADIC 参数（即接受单独参数并从中形成数组的参数）的 Postgres 函数：

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

如何从 Ruby 传递数组？

假设 id 是一个integer类型：

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

但我只是在学习Ruby。@mu在这个相关的答案中提供了详细的指导：

• 如何在Ruby中将值数组发送到SQL查询？

- Erwin Brandstetter

---

谢谢，这好多了 :) 哦，我该如何在Rails和ActiveModel中实现它？谢谢。 - Eki Eqbal

你的解决方案完美运行，但当我使用空数组时，会出现以下错误：ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = text Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. - Eki Eqbal

@EkiEqbal，正如Erwin所述，在数组后添加“::numeric[]”以避免此问题。 - PinnyM

@EkiEqbal：...或者在这种情况下使用::int[]。 - Erwin Brandstetter

考虑到OP试图通过使用值列表来提高性能（基于假设上述评论中的链接文章仍然正确），答案应该使用格式VALUES (...), (...), ...而不是ARRAY[...]。或者我错过了什么？如果该文章不再正确（或从未正确），那么这个练习似乎没有意义... - PinnyM