Mysql Group by 之Error 1055之坑

Mysql Group by之坑

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

探索异常起因，原因

在执行sql语句的时候出现下面异常

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版本为最新版本

怪不得同事电脑上的没报错，我一运行就报错

解决办法

解决办法有两种

1. 通过mysql的any_value()函数

   select age,any_value(name) from person group by age

   如果是新开发项目这个方法不错，如果是已开发完成的老项目，只是修改mysql版本还是第二种方法更适用, 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服务

然而坑出现了

我测试数据库中的数据

执行查询语句

此时查询不会报错，然而，看看就会发现我库里数据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;