MySQL是否会使IF()函数短路? [英] Does MySQL Short Circuit the IF() function?

问题描述

我需要从第二个表中查询数据，但前提是要满足主表中罕见的一组条件:

I need to query data from a second table, but only if a rare set of conditions in the primary table is met:

SELECT ..., IF(a AND b AND c AND (SELECT 1 FROM tableb ...)) FROM tablea ...

a，b和c条件几乎始终为假，因此我的想法是子查询将永远不会对结果集中的大多数行执行，因此比连接要快得多.但这只有在IF()语句短路的情况下才会成立.

a, b, and c conditions are almost always false, so my thinking is the subquery will never execute for most rows in the result set and thus be way faster than a join. But that would only true if the IF() statement short circuits.

是吗?

感谢您提供的任何帮助.

Thanks for any help you guys can provide.

推荐答案

在J. Jorgenson的帮助下，我想到了自己的测试用例.他的示例没有尝试在条件评估中短路，但是使用他的想法，我提出了自己的测试，并验证了MySQL确实确实使IF()条件检查短路.

With J. Jorgenson's help I came up with my own test case. His example does not try to short circuit in the condition evaluation, but using his idea I came up with my own test and verified that MySQL does indeed short-circuit the IF() condition check.

SET @var:=5;
SELECT IF(1 = 0 AND (@var:=10), 123, @var); #Expected output: 5
SELECT IF(1 = 1 AND (@var:=10), @var, 123); #Expected output: 10

在第二个示例中，MySQL正确短路:@var从不设置为10.

On the second example, MySQL is properly short-circuiting: @var never gets set to 10.

感谢J. Jorgenson的帮助！

Thanks for the help J. Jorgenson!

这篇关于MySQL是否会使IF()函数短路?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！