解析java.sql.SQLSyntaxErrorException异常：Expression #1 of SELECT list is not in GROUP BY clause

「爱情、让人受尽委屈。」 2023-10-12 19:18 4阅读 0赞

大家好，欢迎阅读我们的文章。今天，我们将讨论一个常见的Java异常——java.sql.SQLSyntaxErrorException，并深入探讨其中一个具体的错误信息：Expression \#1 of SELECT list is not in GROUP BY clause。

##### 异常详情 #####

Caused by: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cnpc.T1.id' 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.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
            at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
            at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
            at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
            at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
            at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
            at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
            at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
            at sun.reflect.GeneratedMethodAccessor176.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.lang.reflect.Method.invoke(Method.java:498)
            at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
            at com.sun.proxy.$Proxy161.execute(Unknown Source)
            at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
            at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
            at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
            at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
            at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
            at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
            at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:169)
            at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:197)
            at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:140)
            at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
            at com.sun.proxy.$Proxy238.query(Unknown Source)
            at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
            at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
            at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
            at sun.reflect.GeneratedMethodAccessor304.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.lang.reflect.Method.invoke(Method.java:498)
            at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
            ... 155 common frames omitted

##### 异常说明 #####

这个错误是由于你的SQL查询中的SELECT列表中的表达式不在GROUP BY子句中，并且包含了非聚合列’cnpc.T1.id’，这个列在GROUP BY子句中的列上没有函数依赖关系。这与sql\_mode=only\_full\_group\_by不兼容。

##### 解决方案 #####

你可以采取以下几种方法来解决这个问题：

*  在GROUP BY子句中包含所有非聚合列：根据错误信息，你可以尝试在GROUP BY子句中包含’cnpc.T1.id’列，即使它不是聚合列。这样做可以满足only\_full\_group\_by模式的要求。
 *  使用聚合函数：如果你不想在GROUP BY子句中包含’cnpc.T1.id’列，你可以考虑使用聚合函数来处理该列的值。例如，你可以使用MAX()函数获取该列的最大值或使用GROUP\_CONCAT()函数将该列的值连接成一个字符串。
 *  修改sql\_mode：如果你不需要启用only\_full\_group\_by模式，你可以修改数据库的sql\_mode设置，将其更改为允许非聚合列在SELECT列表中。具体的修改方法可能因数据库系统而异，请参考你使用的数据库系统的文档。以下是mysql5.7.33中的示例：

# 查看sql_mode
    select @@global.sql_mode;
    # 设置sql_mode
    set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

根据你的具体需求和查询逻辑，选择适合的解决方法来修复这个错误。

解析java.sql.SQLSyntaxErrorException异常：Expression #1 of SELECT list is not in GROUP BY clause

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql错误-1055 - Expression #1 of SELECT list is not in GROUP BY clause 解决方案

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关解析java.sql.SQLSyntaxErrorException异常：Expression #1 of SELECT list is not in GROUP BY clause

相关 Caused by: java.sql.SQLSyntaxErrorException: Expression #16 of SELECT list is not in GROUP BY clause

相关 java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contain

相关 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause...

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

相关 MySQL：错误Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate