SQL_MODE = ONLY_FULL_GROUP_BY

发表于   |   分类于

今天在写 SQL 语句的时候遇到了问题。

1
2
SELECT * FROM (SELECT * FROM Employee ORDER BY Salary desc) AS a
GROUP BY a.DepartmentId

执行上面的语句会报错：

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

这是由于 SQL 的 ONLY_FULL_GROUP_BY 模式要求 SELECT 的字段满足下列条件之一：

  • 字段出现在 GROUP BY 中
  • 聚合函数

之所以有这种要求，可以参见 MySQL 高性能第三版关于这方面的阐述：

在分组查询的 SELECT 中直接使用非分组列（上表以 status 分组，但是用的是非分组列 name）通常不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。

举个例子：

Employee Table

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+  
| Id | Name  | Salary | DepartmentId |  
+----+-------+--------+--------------+  
| 1  | Joe   | 70000  | 1            |   
| 2  | Henry | 80000  | 2            |  
| 3  | Sam   | 60000  | 2            |  
| 4  | Max   | 90000  | 1            |  
+----+-------+--------+--------------+

如果使用上述语句对 DepartmentId 进行分组，由于每个分组中有多条数据，SQL 无法判断返回分组的哪一条数据。而「分组字段」和「聚合函数」却能够保证返回的记录是唯一的。

注：只有 MySQL 5.7.x 以上的版本是默认开启 ONLY_FULL_GROUP_BY 模式的。低版本的 MYSQL 数据库是不会有这样的问题的。

解决方法

解决这一问题有以下几种方式：

  1. 修改代码

  2. MySQL版本回退

  3. 修改 SQL 配置文件

查看配置：

1
SELECT @@sql_mode

增加配置：

1
SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

删除配置：

1
SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

因此，我们可以执行下述语句来改变 MYSQL 的 ONLY_FULL_GROUP_BY 配置：

1
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));