MySQL中GROUP BY隱式排序是什么概念呢？ 主要是其它RDBMS沒有這樣的概念，如果沒有認真了解過概念，對這個概念會感覺有點困惑，我們先來看看官方檔案的介紹：
官方檔案MySQL 5.7 Reference Manual中的“.2.1.14 ORDER BY Optimization”章節有如下介紹：
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.
默認情況下GROUP BY隱式排序(即，缺少GROUP BY列的ASC或DESC指示符)，但是，不推薦依賴于隱式GROUP BY排序(即，在沒有ASC或DESC指示符的情況下排序)或GROUP BY的顯式排序(即，通過對GROUP BY列使用顯式ASC或DESC指示符)，要生成給定的排序 ORDER，請提供ORDER BY子句，
從MySQL 8.0開始，GROUP BY欄位不再支持隱式排序. 官方檔案MySQL 8.0 Reference Manual中“8.2.1.16 ORDER BY Optimization”章節有如下介紹：
Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
那么來看看MySQL的GROUP BY隱式排序（GROUP BY sorted implicitly）吧，我們用“Removal of implicit and explicit sorting for GROUP BY”這篇博客中的例子，
下面實驗環境為MySQL 5.6.41（）
mysql> select version() from dual;
+------------+
| version() |
+------------+
| 5.6.41-log |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t (id INTEGER, cnt INTEGER);
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO t VALUES (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Query OK, 12 rows affected (0.00 sec)Records: 12 Duplicates: 0 Warnings: 0
#MySQL在這里隱式地對GROUP BY的結果進行排序（即在缺少GROUP BY列的ASC或DESC指示符的情況下），
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id; --GROUP BY隱式排序
+------+----------+
| id | SUM(cnt) |+------+----------+
| 1 | 13 |
| 2 | 9 |
| 3 | 12 |
| 4 | 6 |
+------+----------+
4 rows in set (0.00 sec)
MySQL還支持使用GROUP BY進行顯式排序（即通過對GROUP BY列使用顯式ASC或DESC指示符）
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id DESC; --GROUP BY顯式排序
+------+----------+
| id | SUM(cnt) |+------+----------+
| 4 | 6 |
| 3 | 12 |
| 2 | 9 |
| 1 | 13 |
+------+----------+
4 rows in set (0.00 sec)
從MySQL8.0開始，MySQL不再支持GROUP BY的隱式或顯示排序，如下所示：
#下面實驗環境為MySQL 8.0.18
mysql> select version();+-----------+
| version() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t (id INTEGER, cnt INTEGER);
Query OK, 0 rows affected (0.39 sec)mysql> INSERT INTO t VALUES (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Query OK, 12 rows affected (0.10 sec)Records: 12 Duplicates: 0 Warnings: 0
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id;
+------+----------+
| id | SUM(cnt) |+------+----------+
| 4 | 6 |
| 3 | 12 |
| 1 | 13 |
| 2 | 9 |
+------+----------+
4 rows in set (0.00 sec)
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1
如上所示，GROUP BY隱式排序不支持了，在MySQL 8.0中，上面測驗例子是無序的，GROUP BY顯示排序則直接報錯，所以如果有資料庫從MySQL 5.7或之前的版本，遷移升級到MySQL 8的話，就需要特別留意這個問題了，正確的做法應該是GROUP BY .. ORDER BY 這種操作，如下所示：
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id
-> ORDER BY id;
+------+----------+
| id | SUM(cnt) |+------+----------+
| 1 | 13 |
| 2 | 9 |
| 3 | 12 |
| 4 | 6 |
+------+----------+
4 rows in set (0.00 sec)
mysql>
MySQL 8.0.13中洗掉了GROUP BY的顯式排序，至于為什么MySQL 8.0不再支持GROUP BY的隱式排序和顯示排序，這篇文章“Removal of implicit and explicit sorting for GROUP BY”已經有詳細的介紹，這里不畫蛇添足了，
參考資料：
https://mysqlserverteam.com/removal-of-implicit-and-explicit-sorting-for-group-by/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109164.html
標籤：MySQL