MySQL8.0新特性--Group by

相同点：
（1）去掉重复值：根据group by后面的关键字只显示一行结果；

（2）默认开启参数ONLY_FULL_GROUP_BY，表示完全group by，即select后面跟的列group by后面也必须有，但是group by后面跟的列，select后面不一定需要出现；

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)
 
mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
 
mysql> select * from t_group;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  22744 | d006    | 1986-12-01 | 9999-01-01 |
|  24007 | d005    | 1986-12-01 | 9999-01-01 |
|  30970 | d005    | 1986-12-01 | 2017-03-29 |
|  31112 | d002    | 1986-12-01 | 1993-12-10 |
|  40983 | d005    | 1986-12-01 | 9999-01-01 |
|  46554 | d008    | 1986-12-01 | 1992-05-27 |
|  48317 | d008    | 1986-12-01 | 1989-01-11 |
|  49667 | d007    | 1986-12-01 | 9999-01-01 |
|  50449 | d005    | 1986-12-01 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
+--------+---------+------------+------------+
10 rows in set (0.00 sec)
 
mysql> select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006    |        1 |
| d005    |        4 |
| d002    |        1 |
| d008    |        2 |
| d007    |        1 |
| d004    |        1 |
+---------+----------+
6 rows in set (0.00 sec)
 
mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.t_group.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 
关闭ONLY_FULL_GROUP_BY参数后，不报错，但是结果是不完全group by;
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)
 
mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;
+---------+--------+----------+
| dept_no | emp_no | count(*) |
+---------+--------+----------+
| d006    |  22744 |        1 |
| d005    |  24007 |        4 |
| d002    |  31112 |        1 |
| d008    |  46554 |        2 |
| d007    |  49667 |        1 |
| d004    |  10004 |        1 |
+---------+--------+----------+
6 rows in set (0.00 sec)

不同点：mysql5.7group by 默认还有排序功能，8.0默认只分组不排序，需要加order by才排序，这点可以从执行结果是否有Using filesort来判断

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)
 
mysql> select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006    |        1 |
| d005    |        4 |
| d002    |        1 |
| d008    |        2 |
| d007    |        1 |
| d004    |        1 |
+---------+----------+
6 rows in set (0.00 sec)
 
mysql> desc select dept_no,count(*) from t_group group by dept_no;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
 
 
root@localhost [testdb]>select @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)
 
root@localhost [testdb]>select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
| d004    |        1 |
| d005    |        4 |
| d006    |        1 |
| d007    |        1 |
| d008    |        2 |
+---------+----------+
6 rows in set (0.00 sec)
 
root@localhost [testdb]>desc select dept_no,count(*) from t_group group by dept_no;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)