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

问题描述

在MySQL中执行子句的预定义顺序是什么?其中一些是在运行时确定的，并且此顺序正确吗?

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

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

推荐答案

MySQL语句的实际执行有些棘手.但是，该标准确实指定了查询中元素解释的顺序.尽管我认为HAVING和GROUP BY可能会在SELECT之后出现，但基本上按照您指定的顺序进行:

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子句
• WHERE子句
• SELECT子句
• GROUP BY子句
• HAVING子句
• ORDER BY子句

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

这对于了解如何解析查询很重要.例如，不能使用在WHERE子句中的SELECT中定义的列别名，因为WHERE是在SELECT之前解析的.另一方面，这样的别名可以在ORDER BY子句中.

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

和

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

都具有完全不执行ORDER BY的效果-因此不会在GROUP BY之后执行(在第一种情况下，效果是从GROUP BY中删除了排序，在第二种情况中是效果就是不做GROUP BY已经做的事情.

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