原

解决MySql查询语句报Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre异常的问题

2018年08月11日 11:10:02 阅读数：4803
<article class="baidu&#95;pl">

今天上午在公司启动项目的时候，获取一个列表的时候项目报错，内容如下：

</article> 
 
    
  
  1. 
   
    
    
   
    
   
    
    
    
     Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #
     1 of SELECT list 
     is not 
     in GROUP BY clause and contains nonaggregated column 
     'kypj-gate.t1.id' which 
     is not functionally dependent on columns 
     in GROUP BY clause; 
     this 
     is incompatible with sql_mode=only_full_group_by
    
    
   
    2. 
  
  2. 
   
    
    
   
    
   
    
    
    
     ; bad SQL grammar []; nested exception 
     is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #
     1 of SELECT list 
     is not 
     in GROUP BY clause and contains nonaggregated column 
     'kypj-gate.t1.id' which 
     is not functionally dependent on columns 
     in GROUP BY clause; 
     this 
     is incompatible with sql_mode=only_full_group_by
    
    
   
    3. 
  
  3. 
   
    
    
   
    
   
    
    
    
      at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:
     91)
    
    
   
    4. 
  
  4. 
   
    
    
   
    
   
    
    
    
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
     73)
    
    
   
    5. 
  
  5. 
   
    
    
   
    
   
    
    
    
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
     81)
    
    
   
    6. 
  
  6. 
   
    
    
   
    
   
    
    
    
      at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:
     74)
    
    
   
    7. 
  
  7. 
   
    
    
   
    
   
    
    
    
      at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:
     399)
    
    
   
    8. 
  
  8. 
   
    
    
   
    
   
    
    
    
      at com.sun.proxy.$Proxy35.selectList(Unknown Source)
    
    
   
    9. 
  
  9. 
   
    
    
   
    
   
    
    
    
      at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:
     205)
    
    
   
    10. 
  
  10. 
   
    
    
   
    
   
    
    
    
      at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:
     119)
    
    
   
    11. 
  
  11. 
   
    
    
   
    
   
    
    
    
      at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
     63)
    
    
   
    12. 
  
  12. 
   
    
    
   
    
   
    
    
    
      at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:
     52)
    
    
   
    13. 
  
  13. 
   
    
    
   
    
   
    
    
    
      at com.sun.proxy.$Proxy43.findProxyList(Unknown Source)
    
    
   
    14. 
  
  14. 
   
    
    
   
    
   
    
    
    
      at com.kypj.jeeplat.modules.platmgt.player.service.PlatPlayerService.findProxyListPage(PlatPlayerService.java:
     2268)
    
    
   
    15.

明明是相同的项目在同事的电脑上就没问题，在网上查了一下原来是MySql版本的问题，我的MySql数据库版本是5.7.23，在5.7以上的版本实现了对功能依赖的检测，MySql默认启用了only_full_group_by SQL模式，可以试试在Navicat里面输入查询语句

select @@global.sql_mode;

得到的结果如下，

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

照理说只要将结果中的ONLY_FULL_GROUP_BY去掉就可以，试着在Navicat里执行

set @@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';

重启MySql服务，再进入Navicat执行select @@global.sql_mode发现刚才的set语句并没有生效，

进入MySql安装路径下，打开my.ini文件，在文件的最末尾加上

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

这样一句，再重启MySql服务，发现Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre异常的问题已经解决