如何使用Perl的DBI绑定查询选择空行？

Question

我希望能够将某些内容传递到SQL查询中，以确定是否只选择其中某一列为空的数据。如果我只是构建查询字符串而不使用绑定变量，我会这样做：

if ($search_undeleted_only)
{
    $sqlString .= " AND deleted_on IS NULL";
}

但是我想使用绑定查询。这是否是最好的方法？

my $stmt = $dbh->prepare(...
    "AND (? = 0 OR deleted_on IS NULL) ");
$stmt->execute($search_undeleted_only);

- Paul Tomblin

Answer 1

是的，如果您有X个潜在的筛选器，其中一些是可选的，那么模板应该说“AND（？=-1 OR some_field =？）”，并创建一个特殊函数来包装执行调用并绑定所有第二个问号。（在这种情况下，-1是一个特殊值，表示“忽略此筛选器”）。

来自Paul Tomblin的更新：我编辑了答案，包括来自评论的建议。

- Dmitriy

---

这很好，但反过来写（先写?=-1的部分）会更好，因为许多SQL数据库的where子句短路（也就是说，如果OR语句的第一部分为真，则不会再去计算后续部分，因为它不可能计算出除了真以外的结果）。 - Robert C. Barth

希望你们不介意，我会编辑你们的答案，把 ?=-1 放在最前面，以使答案更好。 - Paul Tomblin

顺便提一下，Bill Karwin的回答中有关于查询优化的非常好的信息。我希望我能够接受它们两个。 - Paul Tomblin

Answer 2

所以你依靠布尔表达式的短路语义来调用你的IS NULL条件？这似乎有效。

有趣的一点是，像1 = 0这样没有参数的常量表达式应该由查询优化器分解。在这种情况下，由于优化器不知道表达式是一个常量true还是false，直到执行时间，这意味着它不能将其分解。它必须对每一行评估表达式。

因此，相对于使用非参数化常量表达式，可以假设这会给查询增加一些较小的成本。

然后，将OR与IS NULL表达式结合使用也可能对优化器产生影响。它可能会决定无法从deleted_on索引中受益，而在更简单的表达式中会受益。这取决于您正在使用的RDBMS实现以及数据库中值的分布。

- Bill Karwin

---

@Bill，有关查询优化的信息真的很棒，我希望我能接受您和Dmitriy的答案。 - Paul Tomblin

没关系！我很高兴它有用。 - Bill Karwin

Answer 3

我认为这是一个合理的方法。它很好地遵循了正常的过滤器模式，并且应该具有良好的性能。

- Michael Haren