mysql5.7+中sql_mode的ONLY_FULL_GROUP_BY限制

一、问题

最近在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