only_full_group_by导致的group by错误解决

刷题的时候如果使用group by经常碰见如下报警

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘20220210_test.qu.device_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec

这里我们说明一下原因。

一、group by讲解
group by名为分组，即按照给定列名的不同内容当成不同小组，使用时会对数据按照小组分类，同类数据会先全部展示后才会展示下一类数据

drop table if exists `user_profile`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

–查看一下结果

select * from user_profile group by university;

二、 group by规则
官方文档是 ：所有select的字段，除聚合函数中的字段，都必须在group by中出现。且group

那上面的字段举例子，执行如下语句一定会报错

select university,device_id,count(gpa)
from user_profile
group by university;

原因是，**我们使用了聚合函数，而聚合函数一旦与group by一起使用，那么再除去聚合函数列后我们所查询的表段信息的列名必须全部放到group by里
**
如下才是正确使用

select university,device_id,count(gpa)
from user_profile
group by university,device_id;

三、only_full_group_by的作用
之所以会出现这种情况，是因为我们sql的配置文件my.ini/my.conf做了默认限制限制
sql_mode=“ONLY_FULL_GROUP_BY”

#可以通过如下命令去查看

select @@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

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUP BY合法性的检查。
在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时，一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined。
简单点说就是为了数据查询结果的严谨性才设置如此用法，mysq也有可能是跟风，因为5.7以前的mysql允许出现语义不明确的列查询结果，所以就不默认使用ONLY_FULL_GROUP_BY
MySQL 5.7版本开始默认使用这个语义，就是我们所说的ONLY_FULL_GROUP_BY语义
https://www.cnblogs.com/jiehao-yu/p/14533511.html