【MySQL】only_full_group_by 模式导致的报错 — 解释与解决

1. 问题复现

现有一个数据表 test ，如下：

mysql> select * from test;
+---------+-----------+--------+-----------------+
| dept_no | dept_name | emp_no | title           |
+---------+-----------+--------+-----------------+
| d001    | Marketing |  10001 | Senior Engineer |
| d001    | Marketing |  10002 | Staff           |
| d002    | Finance   |  10003 | Senior Engineer |
+---------+-----------+--------+-----------------+

想统计每个部门员工数量，进行以下操作：

mysql> select dept_no, dept_name, count(emp_no) from test group by dept_no;

报错，错误提示如下：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'practice.test.dept_name' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

 

2. 报错解释

因为 MySQL 默认设置了 “sql_mode=only_full_group_by”，即 “仅全分组模式”，查询时，被 select 的每一列都必须满足下述条件之一：① 该列为被聚合列（即被应用聚合函数的列）或存在于 group by 从句中；② 该列必须 功能上依赖于 group by 指定的列。

回到出错的语句，显然 dept_name 列不满足上面 2 个条件：
① 其既不在 count() 函数中，又不在 group by 从句中；
② 其不 “功能上依赖于” group by 指定的列（即 dept_no 列)。

注：“功能上依赖于”，参考 MySQL 页面，其解释是：“如果列 x 无重复值，且其中每个值都可以唯一确定列 y 中的值，那么列 y 在功能上依赖于列 x”。具体情况是，如果列 x 是 primary key，或者 x 是一个无重复值（unique）且非空（not null）的索引（index），那么其他列会在功能上依赖于列 x。

 

3. 解决办法

(1) 当出错列可由 group by 的列唯一确定，那么对出错列使用 any_value() 即可，其功能相当于一个聚合函数。
比如上例中部门名称 dept_name 一定是由部门代码 dept_no 唯一确定的（但是 dept_no 在原表中有重复值，所以称不上 “功能上依赖于”），对 dept_name 使用 any_value() 不会造成歧义。

mysql> select dept_no, any_value(dept_name), count(emp_no) from test group by dept_no;
+---------+----------------------+---------------+
| dept_no | any_value(dept_name) | count(emp_no) |
+---------+----------------------+---------------+
| d001    | Marketing            |             2 |
| d002    | Finance              |             1 |
+---------+----------------------+---------------+

(2) 如果出错列不可由 group by 的列唯一确定，那么查询本身就是有歧义的。如：

mysql> select dept_no, title, count(emp_no) from test group by dept_no;

上面语句以部门 dept_no 进行分组，且使用 count(emp_no) 使每个部门聚合成 1 行，但是在部门 d001 中，title 字段有 Senior Engineer 和 Staff 共 2 个取值，SQL 不知道选出哪个来显示，于是报错。因此 “sql_mode=only_full_group_by” 的意义就是防止选出不确定的结果。

(3) 如果不在意选出 (2) 中描述的不确定的结果，可以关闭 only_full_group_by 模式，操作如下：
① 查询当前的 sql_mode，如：

mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

② 记录查询到的结果，并在结果后追加 ONLY_FULL_GROUP_BY，如：

mysql> set @@global.sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY;

③ 重启 MySQL 生效。

 

最后

本文中 test 表格的创建代码：

DROP TABLE IF EXISTS test;

CREATE TABLE test (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
PRIMARY KEY (dept_no, emp_no));

INSERT INTO test VALUES('d001', 'Marketing', 10001, "Senior Engineer");
INSERT INTO test VALUES('d001', 'Marketing', 10002, "Staff");
INSERT INTO test VALUES('d002', 'Finance', 10003, "Senior Engineer");

 
参考链接：
[1] 12.20.4 Detection of Functional Dependence