問題描述

在 MySQL 中執行子句的預定義順序是什么?其中一些是在運行時決定的，這個順序是否正確?

• FROM 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• SELECT 子句
• ORDER BY 子句

MySQL 語句的實際執行有點棘手.但是，該標準確實指定了查詢中元素的解釋順序.這基本上是按照您指定的順序，盡管我認為 HAVING 和 GROUP BY 可以在 SELECT 之后:

• FROM 子句
• WHERE 子句
• SELECT 子句
• GROUP BY 子句
• HAVING 子句
• ORDER BY 子句

這對于理解查詢的解析方式很重要.例如，您不能在 WHERE 子句中使用 SELECT 中定義的列別名，因為 WHERE 在 SELECT 之前被解析.另一方面，這樣的別名可以在 ORDER BY 子句中.

至于實際執行，這真的取決于優化器.例如:

和

兩者都有 ORDER BY 根本沒有被執行的效果——所以在 GROUP BY 之后不會被執行(在第一種情況下，效果是從 GROUP BY 中刪除排序，第二個效果是只做 GROUP BY 已經做的事情).

What is the predefined order in which the clauses are executed in MySQL? Is some of it decided at run time, and is this order correct?

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

The actual execution of MySQL statements is a bit tricky. However, the standard does specify the order of interpretation of elements in the query. This is basically in the order that you specify, although I think HAVING and GROUP BY could come after SELECT:

• FROM clause
• WHERE clause
• SELECT clause
• GROUP BY clause
• HAVING clause
• ORDER BY clause

This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECT in the WHERE clause, for instance, because the WHERE is parsed before the SELECT. On the other hand, such an alias can be in the ORDER BY clause.

As for actual execution, that is really left up to the optimizer. For instance:

. . .
GROUP BY a, b, c
ORDER BY NULL

and

. . .
GROUP BY a, b, c
ORDER BY a, b, c

both have the effect of the ORDER BY not being executed at all -- and so not executed after the GROUP BY (in the first case, the effect is to remove sorting from the GROUP BY and in the second the effect is to do nothing more than the GROUP BY already does).

這篇關于MySQL查詢/子句執行順序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！