【MySQL】only_full_group_by导致的group by错误解决方案。

问题描述：

由于MySQL5.7以上版本，默认是开启了 【only_full_group_by】模式的：

select @@sql_mode;
select @@global.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

这样就导致原先带有【group by】的很多SQL报错：

java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tmp.PERSON_NAME' 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:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1020)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3240)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_executeQuery(WallFilter.java:648)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)

问题原因：

MySQL在低版本（5.7.x 以下）中允许 select 后面的非聚合列不出现在 group by 中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错：

select name, age, count(name) from student group by age

因为在SQL标准中，出现在select target list中，并且没有出现在聚集函数中的表达式必须出现在group by子句中：

1、group by后面的列必须是在select中存在。

2、select、having或order by后面存在的非聚合列必须全部在group by中存在。

而没有遵循原则的sql会被认为是不合法的sql，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，MySQL 5.7开始修正，即ONLY_FULL_GROUP_BY语义。

解决方案：

一、生产环境中要求必须开启ONLY_FULL_GROUP_BY的：

MySQL提供了any_value(field) 函数允许非分组字段的出现（和关闭 only_full_group_by 模式有相同效果）。

对未在 group by 中的字段使用 any_value修饰。一般是根据报错字段进行修饰。

select any_value(name), age, count(name) from student group by age

二、可以允许关闭ONLY_FULL_GROUP_BY的：

1、修改MySQL程序配置，Linux修改my.conf文件，Windows修改my.ini文件【需要重启MySQL服务】：

删除配置文件中sql_mode后的only_full_group_by配置。

[mysqld]

sql_mode=去除only_full_group_by后的值。

修改完后重启MySQL服务。

2、通过sql修改，不需要重启服务。

查看sql_model的配置：select @@global.sql_mode

设置新值：set global sql_mod='去除only_full_group_by后的值'

重启后失效。

可根据项目需求，选择适合自己的修改方式。