Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column xxxx
程序员文章站
2023-12-28 14:55:46
...
今天测试突然给我提了一个bug,说系统异常。我心想,我明明在开发环境测试过了,没有问题,怎么到了测试环境就有问题了,于是,我登录测试环境的服务器看日志,发现一个异常日志,然后我把sql复制下来,粘贴到开发环境的数据库执行了一下,咦!没有问题,把他放在测试环境数据库执行,有问题,然后我看一下数据库的版本号一样的,然后我看了下sql_mode
的配置,发现配置不一致,最后我将测试环境的配置改了一下,ok,没有问题了。哈哈哈
异常日志:
Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘sss.month_id’ which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
查询sql_mode
select @@global.sql_mode;
修改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';
总结
mysql 5.7.x版本,默认开启了only_full_group_by模式,但是开启这个模式后,原先的group by 语句就报错,我的解决办法就是将ONLY_FULL_GROUP_BY
去掉,但是还有另外一种办法,可以看下面的连接
这里有一篇文章挺不错的:https://www.cnblogs.com/kenshinobiy/p/9580701.html
推荐阅读
-
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column xxxx
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
-
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
-
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains解决
-
idea报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
【问题解决】Expression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zxj
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.