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

最新推荐文章于 2025-08-04 17:02:23 发布

Backcanhave7

最新推荐文章于 2025-08-04 17:02:23 发布

阅读量1w

点赞数 3

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_41080850/article/details/84936410

MySQL 专栏收录该内容

38 篇文章

订阅专栏

本文详细介绍了在MySQL5.7.22版本中，使用ORDER BY进行排序时的特性，特别是在与SELECT DISTINCT结合使用时的行为。通过具体示例展示了即使排序字段不在SELECT语句中，查询也能正确执行的情况，同时解释了ONLY_FULL_GROUP_BY SQL模式下，ORDER BY对非聚合列的影响。

说明：本文测试时使用的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