MySQL 8.0 only full group by 问题处理

从MySQL5.7升级为MySQL8.0后，应用代码运行起来可能会遇到这样的问题

java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.a.xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar:1.1.10]

出现这个问题是因为MySQL8.0默认的sql_mode设置了ONLY_FULL_GROUP_BY

解决方法：

去掉ONLY_FULL_GROUP_BY，设置新的sql_mode值

1.临时生效

a.查看当前sql_mode的值

select @@sql_mode;

例如：

将查询结果复制出来

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

去掉ONLY_FULL_GROUP_BY, （逗号也一并去掉）

再将剩余的值设置回去

b. 设置新的sql_mode

set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
,NO_ENGINE_SUBSTITUTION';

2.永久生效

永久生效需要修改mysql的配置文件。

在配置文件 /etc/mysql/my.cnf 中 [mysqld] 下面加上一行。设置sql_mode，设置值与临时设置的值一样。

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER ,NO_ENGINE_SUBSTITUTION'

备注：

Linux下：mysql配置文件在 /etc/my.cnf

Windows下：mysql配置文件为 my.ini

Docker版本的MySQL，配置文件在docker里/etc/mysql/my.cnf

转至：https://zhuanlan.zhihu.com/p/81246677