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屋!