mysql升级到 5.7后分组查询的问题

港控/mmm° 2022-03-25 14:14 217阅读 0赞

在5.7以上的版本实现了对功能依赖的检测，MySql默认启用了only\_full\_group\_by SQL模式:

21-Jan-2019 11:59:54.000 SEVERE \[catalina-exec-24\] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet \[dispatherServlet\] in context with path \[/PayMentSystem\] threw exception \[Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:   
\#\#\# Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression \#4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'examination.s.payDay' 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 file \[/usr/tomcat/tomcat8/webapps/PayMentSystem/WEB-INF/classes/com/yc/mapper/ExcelReportModelMapper.xml\]  
\#\#\# The error may involve com.yc.mapper.ExcelReportModelMapper.financeClass-Inline  
\#\#\# The error occurred while setting parameters  
\#\#\# SQL: select a.className className,a.editionName editionName,a.studentNum studentNum,a.contractedAmountTotal contractedAmountTotal,b.totalFee totalFee ,a.aId,b.payDay    from (select ec.className as className,ec.Id as aId,ec.editionName as editionName,count(e.id) as studentNum ,sum(e.contractedAmount) as contractedAmountTotal,ec.Id as Id from examineeclass ec inner join examinee e on ec.Id =e.classId group by ec.Id) a   inner JOIN    (select bec.Id as bId,bec.editionName as editionName,sum(s.fee) as totalFee,s.payDay as payDay   from examineeclass bec   inner join examinee e   on bec.Id =e.classId   inner join studentfees s   on s.id=e.id   where s.isIdentified=1   group by bec.Id    having          bec.Id=?     or     bec.Id=?     or     bec.Id=?           ) b    on a.aId=b.bId  
\#\#\# Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression \#4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'examination.s.payDay' 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 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression \#4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'examination.s.payDay' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by\] with root cause  
 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression \#4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'examination.s.payDay' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

[在/etc/mysql/my.cnf  中，][etc_mysql_my.cnf]在文件的最末尾加上

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服务

[etc_mysql_my.cnf]: https://blog.csdn.net/zhangyingchengqi/article/details/86572403