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

这篇具有很好参考价值的文章主要介绍了MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、异常错误

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

expression #2 of select list is not in group by clause and contains nonaggre,mysql,数据库

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

expression #2 of select list is not in group by clause and contains nonaggre,mysql,数据库

三、解决方法

在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

expression #2 of select list is not in group by clause and contains nonaggre,mysql,数据库

重启mysqld服务

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

成功

expression #2 of select list is not in group by clause and contains nonaggre,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-821962.html

到了这里，关于MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！