MySQL8.0新特征--Group by

2024-07-24 12:34:59

字体：大中小

来源：转载

供稿：网友

　　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”.

（编辑：武林网）

上一篇：MySQL必知定会过滤数据

下一篇：mysqldump恢复备份出现的难题