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 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
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是否能排序會直接影響分頁查詢結(jié)果
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有關(guān)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”.
