是否评估了 SQL WHERE 子句短路？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/789231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is the SQL WHERE clause short-circuit evaluated?
提问by Greg Dean
Are boolean expressions in SQL WHERE clauses short-circuit evaluated ?
SQL WHERE 子句中的布尔表达式是否短路计算 ？
For example:
例如：
SELECT *
FROM Table t
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key)
If @key IS NULLevaluates to true, is @key IS NOT NULL AND @key = t.Keyevaluated?
如果@key IS NULL计算结果为真，是否对@key IS NOT NULL AND @key = t.Key进行计算？
If No, why not?
如果不是，为什么不呢？
If Yes, is it guaranteed? Is it part of ANSI SQL or is it database specific?
如果是，是否有保证？它是 ANSI SQL 的一部分还是特定于数据库的？
If database specific, SqlServer? Oracle? MySQL?
如果数据库特定，SqlServer？甲骨文？MySQL？
采纳答案by Greg Dean
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
ANSI SQL 草案 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...]
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependentwhether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
6.3.3.3 规则评估顺序
[...]
如果优先级不是由格式或括号决定的，则表达式的有效计算通常是从左到右进行的。但是， 表达式是否实际从左到右求值取决于实现，特别是当操作数或运算符可能导致条件引发时，或者如果可以在不完全求值表达式的所有部分的情况下确定表达式的结果。
回答by PMc
From the above, short circuiting is not really available.
综上所述，短路并不是真正可用的。
If you need it, I suggest a Case statement:
如果您需要，我建议使用 Case 语句：
Where Case when Expr1 then Expr2 else Expr3 end = desiredResult
Expr1is always evaluated, but only one of Expr2and Expr3will be evaluated per row.
Expr1总是被评估，但每行只会评估其中一个Expr2和Expr3。
回答by dkretz
I think this is one of the cases where I'd write it as if it didn't short-circuit, for three reasons.
我认为这是我将它写成没有短路的情况之一，原因有三个。
Because for MSSQL, it's not resolved by looking at BOL in the obvious place, so for me, that makes it canonically ambiguous.
because at least then I know my code will work. And more importantly, so will those who come after me, so I'm not setting them up to worry through the same question over and over again.
I write often enough for several DBMS products, and I don't want to have to remember the differences if I can work around them easily.
因为对于 MSSQL，它不能通过在明显的地方查看 BOL 来解决，所以对我来说，这使得它在规范上模棱两可。
因为至少那时我知道我的代码会起作用。更重要的是，那些追随我的人也会如此，所以我不会让他们一遍又一遍地担心同样的问题。
我经常为几个 DBMS 产品编写代码，如果我可以轻松解决它们，我不想必须记住它们之间的差异。
回答by Mehmet Aras
I don't believe that short circuiting in SQL Server (2005) is guaranteed. SQL Server runs your query through its optimization algorithm that takes into account a lot of things (indexes, statistics, table size, resources, etc) to come up with an effective execution plan. After this evaluation, you can't say for sure that your short circuit logic is guaranteed.
我不相信 SQL Server (2005) 中的短路是有保证的。SQL Server 通过其优化算法运行您的查询，该算法考虑了许多因素（索引、统计信息、表大小、资源等）以提出有效的执行计划。经过这次评估，你不能肯定地说你的短路逻辑是有保证的。
I ran into the same question myself sometime ago and my research really did not give me a definitive answer. You may write a small query to give you a sense of proof that it works but can you be sure that as the load on your database increases, the tables grow to be bigger, and things get optimized and changed in the database, that conclusion will hold. I could not and therefore erred on the side of caution and used CASE in WHERE clause to ensure short circuit.
前段时间我自己也遇到了同样的问题，我的研究确实没有给我一个明确的答案。您可以编写一个小查询来证明它有效，但是您能否确定随着数据库负载的增加，表变得更大，并且数据库中的事情得到优化和更改，该结论将抓住。我不能，因此在谨慎方面犯了错误，并在 WHERE 子句中使用了 CASE 以确保短路。
回答by Logicalmind
You have to keep in mind how databases work. Given a parameterized query the db builds an execution plan based on that query without the values for the parameters. This query is used every time the query is run regardless of what the actual supplied values are. Whether the query short-circuits with certain values will not matter to the execution plan.
您必须牢记数据库的工作方式。给定一个参数化查询，db 会根据该查询构建一个执行计划，而没有参数值。无论实际提供的值是什么，每次运行查询时都会使用此查询。查询是否短路与某些值对执行计划无关紧要。
回答by p.campbell
I typically use this for optional parameters. Is this the same as short circuiting?
我通常将它用于可选参数。这和短路一样吗？
SELECT [blah]
FROM Emp
WHERE ((@EmpID = -1) OR (@EmpID = EmpID))
This gives me the option to pass in -1 or whatever to account for optional checking of an attribute. Sometimes this involves joining on multiple tables, or preferably a view.
这使我可以选择传入 -1 或其他任何内容来说明对属性的可选检查。有时这涉及连接多个表，或者最好是一个视图。
Very handy, not entirely sure of the extra work that it gives to the db engine.
非常方便，不完全确定它为数据库引擎提供的额外工作。
回答by tetranz
For SQL Server, I think it depends on the version but my experience with SQL Server 2000 is that it still evaluates @key = t.Key even when @key is null. In other words, it does not do efficient short circuiting when evaluating the WHERE clause.
对于 SQL Server，我认为这取决于版本，但我对 SQL Server 2000 的经验是，即使 @key 为空，它仍然评估 @key = t.Key。换句话说，它在评估 WHERE 子句时没有进行有效的短路。
I've seen people recommending a structure like your example as a way of doing a flexible query where the user can enter or not enter various criteria. My observation is that Key is still involved in the query plan when @key is null and if Key is indexed then it does not use the index efficiently.
我见过有人推荐像您的示例这样的结构作为执行灵活查询的一种方式，用户可以输入或不输入各种条件。我的观察是，当@key 为空时，Key 仍然参与查询计划，如果 Key 被索引，那么它不会有效地使用索引。
This sort of flexible query with varying criteria is probably one case where dynamically created SQL is really the best way to go. If @key is null then you simply don't include it in the query at all.
这种具有不同条件的灵活查询可能是动态创建的 SQL 确实是最佳方式的一种情况。如果@key 为空，那么您根本就不要将它包含在查询中。
回答by stolsvik
Just stumbled over this question, and had already found this blog-entry: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/
刚刚偶然发现了这个问题，并且已经找到了这个博客条目：http: //rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/
The SQL server is free to optimize a query anywhere she sees fit, so in the example given in the blog post, you cannot rely on short-circuiting.
SQL 服务器可以自由地在她认为合适的任何地方优化查询，因此在博客文章中给出的示例中，您不能依赖短路。
However, a CASE is apparently documented to evaluate in the written order - check the comments of that blog post.
但是，显然已记录了 CASE 以按书面顺序进行评估 - 检查该博客文章的评论。
回答by Steve Chambers
Here is a demo to prove that MySQL does perform WHERE clause short-circuiting:
这是一个演示来证明MySQL 确实执行 WHERE 子句短路：
This runs the following queries:
这将运行以下查询：
SELECT myint FROM mytable WHERE myint >= 3 OR myslowfunction('query #1', myint) = 1;
SELECT myint FROM mytable WHERE myslowfunction('query #2', myint) = 1 OR myint >= 3;
The only difference between these is the order of operands in the OR condition.
它们之间的唯一区别是 OR 条件中操作数的顺序。
myslowfunctiondeliberately sleeps for a second and has the side effect of adding an entry to a log table each time it is run. Here are the results of what is logged when running the above two queries:
myslowfunction故意休眠一秒钟，并具有在每次运行时向日志表添加条目的副作用。以下是运行上述两个查询时记录的结果：
myslowfunction called for query #1 with value 1
myslowfunction called for query #1 with value 2
myslowfunction called for query #2 with value 1
myslowfunction called for query #2 with value 2
myslowfunction called for query #2 with value 3
myslowfunction called for query #2 with value 4
The above shows that a slow function is executed more times when it appears on the left side of an OR condition when the other operand isn't always true (due to short-circuiting).
上面显示，当另一个操作数并不总是为真（由于短路）时，当它出现在 OR 条件的左侧时，慢函数会被执行更多次。
回答by zendar
Main characteristic of short circuit evaluation is that it stops evaluating the expression as soon as the result can be determined. That means that rest of expression can be ignored because result will be same regardless it is evaluated or not.
短路评估的主要特点是一旦确定结果就停止评估表达式。这意味着可以忽略表达式的其余部分，因为无论是否对其进行评估，结果都将相同。
Binary boolean operators are comutative, meaning:
二元布尔运算符是可交换的，意思是：
a AND b == b AND a
a OR b == b OR a
a XOR b == b XOR a
so there is no guarantee on order of evaluation. Order of evaluation will be determined by query optimizer.
所以不能保证评估的顺序。评估顺序将由查询优化器确定。
In languages with objects there can be situations where you can write boolean expressions that can be evaluated only with short circuit evaluation. Your sample code construction is often used in such languages (C#, Delphi, VB). For example:
在具有对象的语言中，可能存在您可以编写只能通过短路评估进行评估的布尔表达式的情况。您的示例代码构造经常用于此类语言（C#、Delphi、VB）。例如：
if(someString == null | someString.Length == 0 )
printf("no text in someString");
This C# example will cause exception if someString == nullbecause it will be fully evaluated. In short circuit evaluation, it will work every time.
这个 C# 示例将导致异常 ifsomeString == null因为它将被完全评估。在短路评估中，它每次都会起作用。
SQL operates only on scalar variables (no objects) that cannot be uninitialized, so there is no way to write boolean expression that cannot be evaluated. If you have some NULL value, any comparison will return false.
SQL 仅对无法未初始化的标量变量（无对象）进行操作，因此无法编写无法计算的布尔表达式。如果您有一些 NULL 值，任何比较都将返回 false。
That means that in SQL you cannot write expression that is differently evaluated depending on using short circuit or full evaluation.
这意味着在 SQL 中，您不能编写根据使用短路或完全评估而进行不同评估的表达式。
If SQL implementation uses short circuit evaluation, it can only hopefully speed up query execution.
如果 SQL 实现使用短路评估，它只能有望加速查询执行。
