一、问题
最近在mysql5.7中使用group by进行分组查询时,出现报错
Caused by: org.b3log.latke.repository.RepositoryException: java.sql.SQLSyntaxErrorException: Expression #20 of SELECT xxxx is not in GROUP BY clause and contains nonaggregated column 'xxxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
二、解决方式
网上搜素了一番,找到如下解决方式
1、临时解决
先在mysql的当前会话窗口中SELECT @@global.sql_mode,查询出mysql当前的sql_mode配置,
然后将带有删除线部分的内容删除掉,set @@sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2、根源性解决
linux系统中找到mysql数据库的my.cnf文件,一般默认路径为/etc/my.cnf,
windos中找到mysql安装路径下的my.ini(my-default.ini)文件,一般默认路径为C:\Program Files\MySQL\MySQL Server 5.7,
在[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
重启mysql