首页   >   代码编程   >   JAVA开发

MySQL报错“this is incompatible with sql_mode=only_full_group_by”

在新的linux服务器上安装了mysql5.7之后,项目运行时报错“this is incompatible with sql_mode=only_full_group_by”,详细错误信息如下:

java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in class path resource [mybatis/mapper/AccountMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM account ra LEFT JOIN (SELECT * FROM account GROUP BY company_id) a ON a.company_id = ra.company_id LEFT JOIN company c ON c.id = ra.company_id
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.account.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jwcz.account.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy97.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)

错误提示信息给的很明确,就是在group by的时候的出错了,而且提到了一个ONLY_FULL_GROUP_BY的sqlmode,就是它导致了sql报错,查阅资料后发现,这个是mysql5.7提供的新特性。。。

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode,通过这个sql_mode来提供SQL语句GROUP BY合法性的检查,在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句,MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式,这个表达式的值可能在经过group by操作后变成undefined,而对于语义限制都比较严谨的多家数据库,如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列,这样的语句在这些数据库中是会被报错的,所以从MySQL 5.7版本开始修正了这个语义,就是我们所说的ONLY_FULL_GROUP_BY语义。

使用命令查看一下安装的mysql的sql_mode:

select @@GLOBAL.sql_mode;

果然是有一个ONLY_FULL_GROUP_BY:

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

解决方案:

1、sql语句暂时性修改sql_mode

set @@GLOBAL.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配置文件

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

位置如下图所示,放在[mysqld]下面就行了

MySQL报错“this is incompatible with sql_mode=only_full_group_by”

推荐使用第2种方式,因为第一种在重启了mysql之后,又会被重置掉!!!

QQ群: 686430774  /  718410762

站长Q: 1347384268

如果文章有帮到你,可以考虑请博主喝杯咖啡!

分享到:

欢迎分享本文,转载请注明出处!

作者:不忘初心

发布时间:2019-09-04

永久地址:https://www.jiweichengzhu.com/article/3b12157fcb3e4419a429c1287fa04954

评论