Mysql Group by 之Error 1055之坑

浅浅的花香味﹌ 2022-06-07 06:57 205阅读 0赞

# Mysql Group by之坑 #

*  Mysql Group by之坑
    
     *  探索异常起因原因
        
         *  问题起因
         *  探索原因
         *  解决办法
     *  然而坑出现了
        
         *  存在的问题
         *  总结
     *  Group By正确的用法

在一此开发过程中使用到了group by函数，使用工具为mysql for mac 5.7.17

## 探索异常起因，原因 ##

在执行sql语句的时候出现下面异常  
![异常][SouthEast]

> ERROR 1055 (42000): Expression \#3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘imooc.s.type\_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

### 问题起因 ###

ONLY\_FUll\_GROUP\_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY语句中，也就是说查出来的列必须是GROUP BY之后的字段，或者这个字段出现在聚合函数里面。

### 探索原因 ###

在网上一顿搜索后发现这个错误信息和sql\_mode中的”only\_full\_group\_by”有关，是Mysql5.7 group by新特性，在5.7版本中only\_full\_group\_by这个模式是默认开启的

查看本地mysql版本为最新版本  
![mysql版本][mysql]

### 解决办法 ###

解决办法有两种

1.  通过mysql的any\_value()函数
    
    `select age,any_value(name) from person group by age`
    
    > 如果是新开发项目这个方法不错，如果是已开发完成的老项目，只是修改mysql版本还是第二种方法更适用, [ mysql 官网对any\_value函数句介绍][mysql _any_value]
2.  修改mysql配置my.cnf(windows下面是my.ini)，删除only\_full\_group\_by属性，下面我们通过命令行通过命令进行修改
    
    `SELECT @@GLOBAL.sql_mode;`
    
    把查询出来的sql\_mode中的only\_full\_group\_by删除重新set sql\_model = xxxx即可，然后重新启动mysql服务

## 然而坑出现了 ##

我测试数据库中的数据  
![这里写图片描述][SouthEast 1]

执行查询语句  
![查询结果][SouthEast 2]

此时查询不会报错，然而，看看就会发现我库里数据banji分别是**‘五班’，‘六班’，‘七班’**，这里BITceshi分组分为两组后BITceshi = 0对应着’五班’,’六班’两个值，**而数据库查出来的是只显示五班**

> 数据库查询显示的数据是默认id升序显示的，所以默认正序显示了五班

### 存在的问题 ###

那么问题来了，如果banji 和BIT测试的对应关系不是一对一就会在业务中出现两个问题

*  多次显示的banji的值不同
 *  需要显示的banji值不对

### 总结 ###

**所以对于上面的那种情况如果你现在表数据里面两个需要显示的字段的关系是一对一关系就可以使用上面两种方法解决这个group by error,如果group by字段是一对多的就得谨慎使用**

## Group By正确的用法 ##

myql的group by语法为：  
select 选取分组中的列 + 聚合函数 from 表名称 group by 分组的列  
从语法格式来看，是现有分组，在确定检索的列，检索的列只能是参加分组了的列。  
所以问题中，group by 后的a,b,c是先确定的，而select 后面的字段是可变的  
正确的语句

select a,b,c from table_name group by a,b,c,d;
    select a,b from table_name group by a,b,c;
    select a,max(a) from table_name group by a,b,c;

以下是错误的

select a,b,c from table_name group by a,b;
    select a,b,c from table_name group by a;

[SouthEast]: /images/20220607/f576276cd0b348899d6138f18a8860ba.png
[mysql]: /images/20220607/e51118a0053444d79e2143f819e59946.png
[mysql _any_value]: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
[SouthEast 1]: /images/20220607/b42e23158bc7432d82d778c2b8fd836c.png
[SouthEast 2]: /images/20220607/bedaa2b15a9549b8b57a25d535508b4e.png