MySQL查询/子句执行顺序 [英] MySQL query / clause execution order

问题描述

在 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 子句中.

至于实际执行，这真的取决于优化器.例如:

<预><代码>...按 a、b、c 分组按空排序

和

<预><代码>...按 a、b、c 分组按 a, b, c 排序

两者都有 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查询/子句执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！