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

Myth丶恋晨 2024-03-23 16:22 40阅读 0赞

#### 目录 ####

*  一、异常错误
 *  二、原因
 *  三、解决方法

## 一、异常错误 ##

使用联表查询时，group by两个字段出现了错误

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

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

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

意思是select字段里包含了没有被group by 条件唯一确定的字段。

## 二、原因 ##

MySQL版本5.7之后会检测函数依赖，默认启用的模式是ONLY\_FULL\_GROUP\_BY，使用GROUP BY 语句违背了sql\_mode=only\_full\_group\_by。该模式的意思是只有确定唯一字段的group by才能执行。

使用SQL查询sql\_mode

select @@global.sql_mode

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

## 三、解决方法 ##

在MySQL-server层面修改sql\_mode，在配置文件my.cnf中关闭sql\_mode=ONLY\_FULL\_GROUP\_BY  
，将sql\_mode配置放在 \[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

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

重启mysqld服务

systemctl restart mysqld.service
    systemctl start mysqld.service
    systemctl stop mysqld.service

成功

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

[31103ffab4fb48fe82e32b88780172f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/c210089be24a4e7e9a1cdc8cf1399d74.png
[70b87ab4506545f9960fd97e7b93d347.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/284ee1aac9b94f9a88715a10e378861b.png
[0fbd8a0518eb4b069617142293378955.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/276cd720abb745948a4f49f4fc53387a.png
[227b43ec9acf425c954d3d74dab89cfa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/6462c506bb624545b1d5d8922dc381f0.png
[acd6652c48954c31832f6d63f2882e4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/09571f14a5af4ef5a6382282abd28f80.png

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

发表评论取消回复

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

相关阅读

相关 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

相关 mysql异常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

相关 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 #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.mon

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