mysql only_full_group_by

1.问题

在使用 group by 查询时，sql 语法报错：

SQL_ERROR_INFO: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.device_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

翻译：

SELECT list 不在 GROUP BY 子句中，并且包含未聚合列“a.device_id’在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

MySQL 从 5.7.5 版本开始，默认 SQL 模式包括 ONLY_FULL_GROUP_BY（在 5.7.5 之前，MySQL 不检测函数依赖，并且默认不启用 ONLY_FULL_GROUP_BY）。也就是说咱们在写 SQL 语句有用到 group by 聚合函数时，查询的字段一定要被 group by 所包含。

形如：SELECT A, B FROM tb_xxx GROUP BY A;

默认会报 SQL 语法错误，因为字段 B 不在 GROUP BY 聚合条件内，当某个 A 对应多个 B 时，MYSQL 引擎不知道该返回哪个 B（对 A group by 后只能返回一个 B，以前默认返回第一个出现的 B）。

修改为：SELECT A, B FROM tb_xxx GROUP BY A, B;后可以通过语法检查。但是可能不符合我们的需求，此时应该思考是否真的需要查询字段 B（分组后只会展示第一条 B）。

查询sql_mode

SQL:select @@global.sql_mode;

结果：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

---

2.解决方案

修改sql语句

添加查询字段 B 到 GROUP BY 后，或者去除查询字段 B。

修改sql_mode

一次性修改：

在运行语句时，每次执行的时候前面都加上一下这句SQL，去修改一下 sql_mode

SET@@sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

持久性修改（重启mysql会失效）：

SET global sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

永久性修改：

找到MySQL的安装目录下，打开 my.cnf 文件（windows系统是 my.ini 文件），新增

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

修改后，重启MySQL服务。

---

3.实验

不分组：

分组后只会显示每组第一条数据：

问题背景：

统计每个学校的答过题的用户的平均答题数_牛客网

学校下有学生，学生会回答问题。要求统计每个学校的所有学生平均回答问题数量。

A --> B --> C，一对多对多关系。

• device_id：学生id（唯一标识）
• cnt_avg：所有学生平均回答问题数量

group by增加device_id维度：

结合题目要求，最终的结果应该不需要查询 device_id 字段。

参考：

https://blog.csdn.net/weixin_44273248/article/details/125894349