MySQL中用order by排序的字段不一定要在select语句中

说明：本文测试时使用的MySQL版本是5.7.22

对于5.7.22版本的MySQL，用order by排序的字段不一定要在select语句中，即使是select distinct与order by同时使用。

示例：

现有一张记录学生成绩的grade表：

+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 张无忌 |    85 |
|    2 | 李隆基 |    59 |
|    3 | 王五     |    60 |
|    4 | 曹操     |    79 |
|    5 | 小明     |    90 |
|    6 | 如花     |    60 |
|    7 | 尉迟恭 |  100 |
|    8 | 欧阳风 |    90 |
|    9 | 刘备     |    90 |
|   10 | 董永    |    99 |
|   11 | 冯钰    |    83 |
|   12 | 孙殿英 |   82 |
|    2 | 李隆基  |    59 |
+------+--------+-------+

从grade表中查询出学生的id，姓名信息，并且按照score降序排序：

从图中可以看出查询结果是正确的。

从grade表中查询出不同学生的id，姓名信息，并且按照score降序排序：

从上图可以看出即使select distinct与order by同时使用，查询结果也是正确的，没有出现任何报错。

官方文档中的说明：

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVINGcondition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BYcolumns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

在MySQL5.7.22的配置文件中，SQL mode的默认值为：

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

参考：

https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html