说明:本文测试时使用的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, HAVING
condition, or ORDER BY
list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY
columns. 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