MySQL8.0新特性--Groupby

Group by  语句用于结合聚合函数（如count，sum，avg，max，min），根据一个或多个列对结果集进行分组。

你所需要的网站建设服务，我们均能行业靠前的水平为你提供.标准是产品质量的保证，主要从事成都做网站、网站建设、企业网站建设、移动网站建设、网页设计、成都品牌网站建设、网页制作、做网站、建网站。创新互联拥有实力坚强的技术研发团队及素养的视觉设计专才。

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

（2）MySQL5.7默认开启参数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)

（3）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)

(4) group by是否能排序会直接影响分页查询结果

8.0.13版本
mysql> select dept_no,count(*) from t_group group by dept_no limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006    |        1 |
+---------+----------+
1 row in set (0.01 sec)

5.7.16版本：
root@localhost [testdb]>select dept_no,count(*) from t_group group by dept_no limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
+---------+----------+
1 row in set (0.00 sec)

参考链接

8.2.1.15 GROUP BY Optimization

MySQL 5.7有关group by说明的片段如下：

In MySQL, GROUP BY is used for sorting, so the server may also apply ORDER BY optimizations to grouping. However, relying on implicit or explicit GROUP BY sorting is deprecated. See Section 8.2.1.14, “ORDER BY Optimization”.