ONLY_FULL_GROUP_BY

今天在做group by练习的时候，遇到了一个很有意思的问题，分享一下。

首先，我在MySQL5.0版本的数据库进行如下的操作：

mysql> select sno,sname from student;
+------+-------+
| sno  | sname |
+------+-------+
| 001  | tom   |
| 001  | tom2  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select sno,sname from student group by sno;
+------+-------+
| sno  | sname |
+------+-------+
| 001  | tom   |
+------+-------+
1 row in set (0.01 sec)

mysql> select sno,group_concat(sname) from student group by sno;
+------+---------------------+
| sno  | group_concat(sname) |
+------+---------------------+
| 001  | tom,tom2            |
+------+---------------------+
1 row in set (0.01 sec)

正常情况下，我们希望的是第二种分组查询的效果，即查询之后，将001号的两个姓名放到一起进行显示，但是在书写代码的时候，如果写成select sno,sname from student group by sno;呢？在MySQL5.7.5之前的版本中，就会出现只显示tom,而没有tom2的效果。
为了避免这种情况，MySQL5.7.5及其以上的版本中，默认开启了ONLY_FULL_GROUP_BY，下面是MySQL官方文档对这一操作的解释。

ONLY_FULL_GROUP_BY
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.
For additional discussion and examples, see Section 12.20.3, “MySQL Handling of GROUP BY”.

原文档链接：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
其大致意思就是对于GROUP_BY聚合操作，如果在SELECT中的列既没有在GROUP_BY中出现，本身也不是聚合列（使用SUM，ANG等修饰的列），那么这句SQL是不合法的，因为那一列是不确定的。以上面的操作为例，当仅仅返回name字段的时候，MySQL是不确定到底返回tom还是tom2，所以应该对其进行额外的操作，比如连接起来一起显示。
下面使用5.7.26版本的MySQL数据库进行分组操作：

mysql> select * from employee;
+-------------+---------------+--------------+---------------+
| employee_id | employee_name | employee_age | department_id |
+-------------+---------------+--------------+---------------+
| cc63179     | 楚晨          |           36 |             3 |
| kq62145     | 康琼          |           38 |             1 |
| ll66234     | 李雷          |           35 |             3 |
| ls66124     | 李四          |           26 |             5 |
| wm66243     | 汪梅          |           32 |             3 |
| ww66125     | 王五          |           25 |             2 |
| zl661256    | 赵六          |           30 |             4 |
| zs66123     | 张三          |           28 |             2 |
+-------------+---------------+--------------+---------------+
8 rows in set (0.00 sec)

mysql> # 按照部门对员工姓名进行分组
mysql> select department_id,employee_name from employee group by department_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'offcndb.employee.employee_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> select department_id,group_concat(employee_name) from employee group by department_id;
+---------------+-----------------------------+
| department_id | group_concat(employee_name) |
+---------------+-----------------------------+
|             1 | 康琼                        |
|             2 | 王五,张三                   |
|             3 | 楚晨,李雷,汪梅               |
|             4 | 赵六                        |
|             5 | 李四                        |
+---------------+-----------------------------+
5 rows in set (0.01 sec)