MySQL: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

yufei       3 月, 3 周 前       493

今天在做数据统计的时候遇到这个错误

MySQL: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 

错误愿意大概是:SELECT 的字段列表没有包含在 GROUP BY 语句中

很多年之前都用的好好的,怎么就报错了呢!!!!

查了一下,哦,这又是 MySQL 5.7 的锅。

only_full_group_by

MySQL 其实很早就添加了 only_full_group_by 这个 sql_mode,但一直都作为可选项之一,并没有强制默认。

然而,MySQL 5.7 之后,only_full_group_by 成为 sql_mode 的默认选项之一

这就会让我们的一些惯性思维导致一系列的 SQL 错误

only_full_group_by 这个 sql_mode 的唯一要求,就是所有的 select 字段必须在 group by 字段中,否则必须出现在 SQL Aggregate 函数中,反过来却是非必须的

假设我们有下面一张表 employee

id name depart depart_id salary
1 小明 技术部 1 25000
2 小李 产品部 2 32000
3 小花 技术部 1 35000
4 小红 UI 部 3 28000
CREATE TABLE IF NOT EXISTS `employee`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(64) NOT NULL,
   `depart` VARCHAR(128) NOT NULL,
   `depart_id` INT UNSIGNED NOT NULL,
   `salary` INT UNSIGNED NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO employee VALUES(1,'小明','技术部',1,25000),(2,'小李','产品部',2,32000),(3,'小花','技术部',1,35000),(4,'小红','UI 部',3,28000);

我们要查询每个部门的工资总和,然后又要显示部门名,一种不会触发 only_full_group_by 模式的方法是根据 depart 字段分组

select depart, sum(salary) from employee group by depart

输出结果如下

+-----------+-------------+
| depart    | sum(salary) |
+-----------+-------------+
| 技术部    |       60000 |
| 产品部    |       32000 |
| UI 部     |       28000 |
+-----------+-------------+

但如果我们一点加了 depart_id,那么就会立马报错

mysql> select depart_id,depart, sum(salary) from employee group by depart;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo.employee.depart_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

为什么会这样?

因为字段既不在分组中也不在 Aggregate 函数中,那么在相同的分组中,这个字段的值可能是不同的,系统不知道要如何选择

解决办法

  1. 优化代码,剔除 select 语句中的多余字段,也就是不要触发 only_full_group_by。这是值得推荐的,因为这是写出好代码的必要添加之一。

  2. 告诉系统,你可以随意返回值,也就是使用 any_value() 函数来包装值

    如果你必须要出现这个字段,那么这是推荐的方式

    mysql> select any_value(depart_id),depart, sum(salary) from employee group by depart;
    +----------------------+-----------+-------------+
    | any_value(depart_id) | depart    | sum(salary) |
    +----------------------+-----------+-------------+
    |                    1 | 技术部    |       60000 |
    |                    2 | 产品部    |       32000 |
    |                    3 | UI 部     |       28000 |
    +----------------------+-----------+-------------+
    3 rows in set (0.00 sec)
    
  3. 关闭 sql_mode=ONLY_FULL_GROUP_BY

    在配置文件 my.cnf 中关闭 sql_mode=ONLY_FULL_GROUP_BY

    这是最不推荐的方式!!!!! 千万不要这么做

目前尚无回复
简单教程 = 简单教程,简单编程
简单教程 是一个关于技术和学习的地方
现在注册
已注册用户请 登入
关于   |   FAQ   |   我们的愿景   |   广告投放   |  博客

  简单教程,简单编程 - IT 入门首选站

Copyright © 2013-2018 简单教程 twle.cn All Rights Reserved.