MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx

小咪咪 2023-09-29 12:34 14阅读 0赞

### 一、完整报错内容 ###

1055 - **Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test\_baobiao.qty’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by**, Time: 0.000000s

![在这里插入图片描述][e9a7bb0e718d472e8ff10d1957cbbddd.png]

### 二、原因分析 ###

#### 1、表描述 ####

**1> 表结构**  
![在这里插入图片描述][7312e0477b6b463493b98df20658cde0.png]  
**2> sql语句**

select qty,businessDate,flag, sum(IF(flag=1, 0 - reverseQty, qty)) as sum from test_baobiao GROUP BY tradeOrderLineBO, businessDate;

**3> 返回的报错信息**

> Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test\_baobiao.qty’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

#### 2、分析 ####

从报错信息来看，它告诉我们：select字段里包含了没有被group by 条件唯一确定的字段qty

因为执行`sql_make_group`语句时会把两行纪录id=1和id=2两行记录合并成一行，但是搜索引擎不知道该返回哪一条，所以报错。

#### 3、原因总结 ####

MySQL 5.7.5后only\_full\_group\_by成为sql\_mode的默认选项之一，这可能导致一些sql语句失效。我们只需要把`only_full_group_by`从sql\_mode中移除即可。

### 三、解决方案 ###

#### 1、直接在mysql-cli层面做设置 ####

**1> 先查询出所有的sql\_mode**

select @@global.sql_mode;

![在这里插入图片描述][46a73cac640c45a8ab1adf9ed91a265f.png]

**2> 将ONLY\_FULL\_GROUP\_BY从sql\_mode中移除：**

set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

![在这里插入图片描述][5d7bbd6663644cd2a4ec8618d1e8b4d2.png]  
**3> 重启MySQL客户端**

**如果此种方法无效（博主在navicat上操作有效），采用方法二、直接再MySQL-server侧修改sql\_mode的配置**

#### 二、在MySQL-server层面修改sql\_mode ####

vi /etc/mysql/conf.d/mysql.cnf

在配置文件my.cnf中关闭sql\_mode=ONLY\_FULL\_GROUP\_BY。  
将sql\_mode配置放在 `[mysqld]` 最后，如下：

[mysqld]
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

wq保存文件并退出，最后重启MySQL即可。

service mysql restart

### 四、效果 ###

![在这里插入图片描述][0d55bcc075fd46e69b99be403d2fe927.png]

[e9a7bb0e718d472e8ff10d1957cbbddd.png]: https://img-blog.csdnimg.cn/e9a7bb0e718d472e8ff10d1957cbbddd.png
[7312e0477b6b463493b98df20658cde0.png]: https://img-blog.csdnimg.cn/7312e0477b6b463493b98df20658cde0.png
[46a73cac640c45a8ab1adf9ed91a265f.png]: https://img-blog.csdnimg.cn/46a73cac640c45a8ab1adf9ed91a265f.png
[5d7bbd6663644cd2a4ec8618d1e8b4d2.png]: https://img-blog.csdnimg.cn/5d7bbd6663644cd2a4ec8618d1e8b4d2.png
[0d55bcc075fd46e69b99be403d2fe927.png]: https://img-blog.csdnimg.cn/0d55bcc075fd46e69b99be403d2fe927.png

MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic

相关 MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理

相关 Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘studentsc

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx

相关 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

相关 MySQL：错误Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate