1、记录数据库配置的出现问题

原

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

2018年08月11日 11:10:02 qq_34246315 阅读数：4803

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

 
 

   
   

    
    

   
   
   
   

    
    

     
     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
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     ; 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
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:
     
     91)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
     
     73)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
     
     81)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:
     
     74)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:
     
     399)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at com.sun.proxy.$Proxy35.selectList(Unknown Source)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:
     
     205)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:
     
     119)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
     
     63)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:
     
     52)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at com.sun.proxy.$Proxy43.findProxyList(Unknown Source)
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
     	at com.kypj.jeeplat.modules.platmgt.player.service.PlatPlayerService.findProxyListPage(PlatPlayerService.java:
     
     2268)
    
    
   
   
 
 

明明是相同的项目在同事的电脑上就没问题，在网上查了一下原来是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异常的问题已经解决