vlambda博客
学习文章列表

题图：Photo by Lovro Pavličić on Unsplash

今天是日更第10/15

问题

线上遇到MYSQL GROUP By 查询出现 only_full_group_by 的问题，错误提示是：

MySQLdb._exceptions.OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'train.course_study.company_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

查官方文档，原来是MySQL5.7以上版本一个属性配置变更导致的问题，而我们系统刚好最近升级到8.0。

为什么会出现这种情况呢？我测试环境跑的好好的

原因

我们知道Group By 是用来将字段值相同记录做聚查询的语句，例如将用户表按性别分组， 统计男女各有多少人可以用group by查询

select gender, count(id) from student_user

但是，如果select 查询字段中有非聚合字段，同时该字段没有出现在where条件或group by 语句中， 例如：

SELECT gender, COUNT(id), email FROM student_user GROUP BY gender

上面这条语句把email也可以查出来，在MySQL5.6以下版本不会报错，不过 email 字段你查出来也没有任何意义，也不知道email对应的是哪条记录。

从MySQL5.7开始，系统默认不支持这种查询，这种行为是通过一个sql_mode 设置成 ONLY_FULL_GROUP_BY的配置来控制的。

通过命令可以查到MySQL5.7版本显示：

SELECT @@GLOBAL.sql_mode;

sql_mode中有 ONLY_FULL_GROUP_BY 意味着 select 中的字段要么出现在group by 里面，要么是使用了聚合函数的字段。否则就会报开头那种错误。

回过来看我们实际业务场景中的一个问题

select user_id, company_id, sum(learn_time) from student_user group by user_id

student_user表记录了每个学员多条学习记录，上面这条语句用来统计每个人的总学习时长，user_id 与 company id 是多对一的关系， 就是说一个 user_id 只可能在一个company下面，执行这条语句是希望统计每个user学习时长，顺便把他所在的company_id也找出来。但我并没有把company_id 放在 group by 语句后面。

这时候我们测试环境没问题，数据与预期符合一直。数据库版本是mysql5.6。到了正式环境mysql5.7就无法执行。因为 company_id 出现在查询字段中，但没有出现在where语句或者是 group by 语句中。

解决办法

第一种方式：在 group by 后面加上要查询的非聚合字段， 比如我这里加上 company_id，相当于通过 user_id+comapny_id 来分组，因为user_id和company_id 是多对一关系，所以 分组查询的时候两者返回的结果是一样的。

select user_id, company_id, sum(learn_time) from student_user group by user_id, company_id

第二种方式：使用 any_value 函数， 在非聚合字段上使用函数

select user_id, any_value(company_id), sum(learn_time) from student_user group by user_id

第三种方式：将sql_mode配置中的ONLY_FULL_GROUP_BY去掉， 重启MySQL服务，当然不建议去掉这个配置。这样可以保证你写的SQL更严谨

后记

测试环境与生产环境保持绝对一致的环境，包括服务器和数据库以及依赖的第三方库，提前发现问题。