Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
原文链接:https://blog.csdn.net/hq091117/article/details/79065199
https://blog.csdn.net/allen_tsang/article/details/54892046
MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
比如下表game:
id | group_id | name | score |
---|---|---|---|
1 | A | 小刚 | 20 |
2 | B | 小明 | 19 |
3 | B | 小花 | 17 |
4 | C | 小红 | 18 |
执行sql:
select name, group_id from game group by group_id
(记为sql_make_group)
返回:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'game.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法
-
把group by字段
group_id
设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。 -
使用函数
any_value
把报错的字段name
包含起来。如,select any_value(name), group_id from game group by group_id
。 -
在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY.。msqyl的默认配置是
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
去掉,也可以去掉所有选项设置成sql_mode=
,如果你确信其他选项不会造成影响的话。
成功的步骤:
命令行打开mysql.cnf,可以用whereis查找
sudo vim /etc/mysql/conf.d/mysql.cnf
滚动到文件底部复制并粘贴
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
wq保存并退出
重启MySQL
sudo service mysql restart
执行成功后,返回结果应该是
name | group_id |
---|---|
小刚 | A |
小明 | B |
小红 | C |
为什么默认设置ONLY_FULL_GROUP_BY限制?
对于上述的报错信息,我的理解是select
字段里包含了没有被group by
条件唯一确定的字段name。
因为执行sql_make_group语句实际上把两行纪录小明
和小花
合并成一行,搜索引擎不知道该返回哪一条,所以认为这样的sql是武断的(arbitrary).
解决办法2和3都是禁止检查返回结果的唯一性。
进阶讨论
上述办法可以解决报错的问题,但也说明sql语句本身有逻辑问题。name
字段不应该出现在返回结果,因为它是不确定的。
考虑这样的需求:按group_id
分组后,找出每组得分最少的人。
执行sql: select any_value(name) as name, group_id, min(score) as score from game group by group_id order by min(score)
返回结果
name | group_id | score |
---|---|---|
小明 | B | 17 |
小红 | C | 18 |
小刚 | A | 20 |
B组的name
是小明
(因为小明
的id更小),而期望结果应该是小花
。
所以单纯使用group by
无法实现这样的需求。可以使用临时表的方法:
select id, name,t.group_id, t.score from (select group_id, min(score) as score from game group by group_id order by min(score)) t inner join game on t.group_id=game.group_id and t.score=game.score
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤