SQLAlchemy和空的IN子句

Question

我发现 SQLAlchemy 会把

db.query(...).filter(A.id.in_(ids))

转换成

SELECT ...
FROM a
WHERE a.id != a.id

当 ids 为空时。这会导致对 a 表进行顺序扫描，这显然会对性能造成很大的影响。

第一个问题是：为什么会这样？为什么不直接用 1 = 0 或者其他不需要顺序扫描的方式呢？

第二个，更重要的问题是：有没有常用的解决办法（除了在每个 in_ 前加 if）？

我猜 in_ 可能无法轻易地重新实现，以覆盖所有情况而不引发这个问题，但我不可能是第一个遇到这个问题的人，可能会有一些解决方案可以处理 in_ 的简单和常见用例。

编辑

每次发生这种情况时，SQLAlchemy 都会记录一个警告：

"在 'foo.bar' 上的 IN 语句被调用时序列为空。这导致了一个矛盾，尽管评估这个矛盾可能会很耗费资源。考虑其他策略以提高性能。"

Answer 1

之前被认为是最正确的答案现在已经不再适用了。从 SQL Alchemy 1.2 开始，这种行为可以进行配置，但默认情况下会将这些表达式变成 1 != 1，而不是之前的行为。这一变化是为了应对最初提到的性能问题。

SQLAlchemy 在早期也采用了这种方法，但很快就有人提出，如果“列”的值是 NULL，那么 SQL 表达式中的 IN () 不会被评估为假；相反，这个表达式会返回 NULL，因为“NULL”表示“未知”，而在 SQL 中与 NULL 的比较通常会得到 NULL。

为了模拟这种结果，SQLAlchemy 改变了策略，从使用 1 != 1 改为在空的 IN 表达式中使用 expr != expr，而在空的 NOT IN 表达式中使用 expr = expr；也就是说，不再使用固定的值，而是使用表达式的实际左侧部分。如果表达式的左侧部分评估为 NULL，那么整个比较的结果也会是 NULL，而不是假或真。

不幸的是，用户最终抱怨这种表达式对某些查询计划器的性能影响非常严重。于是，当遇到空的 IN 表达式时，系统添加了一个警告，建议 SQLAlchemy 继续保持“正确”，并敦促用户避免生成空的 IN 条件，因为通常情况下这些条件可以安全省略。然而，对于那些动态构建的查询来说，如果输入的值集合可能为空，这就变得很麻烦了。

• https://docs.sqlalchemy.org/en/14/changelog/migration_12.html#the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified

Answer 2

我遇到这个问题的时候，是因为我在数据库表的一个列上用了枚举类型（Enum）。当我把它改成字符串类型（String）后，问题就解决了。虽然这不是一个真正的解决办法，因为我其实更喜欢用枚举类型，但这样做确实避免了问题。

Answer 3

注意你在问什么：

• 只有当 A.id 的值是可以比较的时候，比较才有意义。如果这个值不存在，那就无法和任何东西比较，所有的比较结果都会是不存在的值，这样就会被认为是 假。也就是说，如果 A.ID 是 NULL，那么 A.ID == 任何东西 的结果是 假，而 A.ID != 任何东西 的结果也是 假：如果 A.ID 是 NULL，那么 A.ID == A.ID || A.ID != A.ID 的结果也是 假。
• 当你用 IN 语句去检查一个空的序列时，实际上是在问这个值是否在一个空列表里。不存在的值不属于任何列表，连空列表也不算。
• 所以你实际上是在询问一些类似于 IS NOT NULL 的东西，以及不属于任何东西的条件。这是需要检查的情况。不存在的值不是某种东西；只有那些不为 NULL 的值才能不成为空列表的成员……
• 因为 sqlalchemy 很聪明，它知道这可能不是你想要表达的条件，所以会给你一个警告。如果序列是空的，你可能应该去掉 IN 语句。

具体的例子可以参考这个 sqlfiddle

如果想要更哲学的理解，可以看看 虚无的本质是什么

Answer 4

我正在使用：

if len(ids) > 0:
    db.query(...).where(A.id.in_(ids))
else:
    db.query(...).where(False)

我试过用 .limit(0) 来代替 .where(false)，但没有成功。空的查询结果在后台有一些不同，这导致了后面其他东西出问题。这个变通方法虽然可能更快，但至少避免了你提到的警告。

Answer 5

（这现在主要是历史上的一个问题，因为SQLAlchemy早就修复了这个错误）。

为了回答提问者的“为什么”，这里有一个常见问题解答（我总觉得这个信息很难找到）：

为什么 .col.in_([]) 会产生 col != col? 为什么不是 1=0?

先简单介绍一下这个问题。SQL中的 IN 操作符，用来比较一个列和一系列元素，通常不接受空列表，也就是说，像这样是有效的：

column IN (1, 2, 3)

但这样就不合法了：

column IN ()

当SQLAlchemy的 Operators.in_() 操作符接收到一个空列表时，会产生这样的表达式：

column != column

从0.6版本开始，它还会发出一个警告，说明会使用一种效率较低的比较操作。这个表达式是唯一一个既不依赖于数据库类型又能产生正确结果的。

举个例子，简单的做法是“直接评估为假，比如比较1=0或1!=1”，但这样处理空值时就不太对了。像这样的表达式：

NOT column != column

在 column IS NULL 时不会返回任何行，但一个不考虑列的表达式，比如：

NOT 1=0

则会返回一行。

正如这篇帖子所示，你可以尝试使用ANY函数来避免这个问题，因为即使是空列表，它的语法也是有效的（不过在SQLite上似乎不支持）。对于大列表来说，它可能也更快，因为构建查询时需要处理的字符串更少。

关于 in_ 操作符的性能问题已经最近被修复，这个修复可能会在SQLAlchemy 1.2.0中发布。