如何使用 Perl 的 DBI 中的绑定查询选择空行？
我希望能够将某些内容传递到 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);
I want to be able to pass something into an SQL query to determine if I want to select only the ones where a certain column is null. If I was just building a query string instead of using bound variables, I'd do something like:
if ($search_undeleted_only)
{
$sqlString .= " AND deleted_on IS NULL";
}
but I want to use bound queries. Would this be the best way?
my $stmt = $dbh->prepare(...
"AND (? = 0 OR deleted_on IS NULL) ");
$stmt->execute($search_undeleted_only);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的; 一个相关的技巧是，如果您有 X 个潜在的过滤器（其中一些是可选的），就是让模板说
" AND (?=-1 OR some_field = ?) "，并创建一个特殊的函数来包装执行调用并绑定所有第二个 ? 。 （在本例中，-1 是一个特殊值，表示“忽略此过滤器”）。Paul Tomblin 的更新：我编辑了答案以包含评论中的建议。
Yes; a related trick is if you have X potential filters, some of them optional, is to have the template say
" AND ( ?=-1 OR some_field = ? ) ", and create a special function that wraps the execute call and binds all the second ?s. (in this case, -1 is a special value meaning 'ignore this filter').Update from Paul Tomblin: I edited the answer to include a suggestion from the comments.
因此，您依赖布尔表达式的短路语义来调用
IS NULL条件？ 这似乎有效。一个有趣的一点是，像
1 = 0这样没有参数的常量表达式应该由查询优化器分解。 在这种情况下，由于优化器直到执行时才知道表达式是常量true还是false，这意味着它无法将其分解出来。 它必须计算每一行的表达式。因此，我们可以假设，相对于使用非参数化常量表达式的成本而言，这会为查询增加较小的成本。
然后将
OR与IS NULL表达式结合起来也可能对优化器产生影响。 它可能会决定它不能从deleted_on上的索引中受益，而在更简单的表达式中它会受益。 这取决于您正在使用的 RDBMS 实现以及数据库中值的分布。So you're relying on short-circuiting semantics of boolean expressions to invoke your
IS NULLcondition? That seems to work.One interesting point is that a constant expression like
1 = 0that did not have parameters should be factored out by the query optimizer. In this case, since the optimizer doesn't know if the expression is a constanttrueorfalseuntil execute time, that means it can't factor it out. It must evaluate the expression for every row.So one can assume this add a minor cost to the query, relative to what it would cost if you had used a non-parameterized constant expression.
Then combining with
ORwith theIS NULLexpression may also have implications for the optimizer. It might decide it can't benefit from an index ondeleted_on, whereas in a simpler expression it would have. This depends on the RDBMS implementation you're using, and the distribution of values in your database.我认为这是一个合理的做法。 它很好地遵循正常的过滤器模式，并且应该提供良好的性能。
I think that's a reasonable approach. It follows the normal filter pattern nicely and should give good performance.