【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后的值'

重启后失效。

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值