mysql 报错解决思考Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column

当遇到MySQL的1055错误时,这意味着SQL查询不符合ONLY_FULL_GROUP_BY模式。这个错误通常出现在尝试在GROUP BY语句中使用未聚合的列。正确的解决方案不是禁用ONLY_FULL_GROUP_BY,而是优化SQL查询,确保SELECT中的所有字段要么在GROUP BY中,要么包含在聚合函数内。博主分享了自己以前的误解,并提供了正确修改SQL的示例,强调了遵循SQL规范的重要性,同时也反思了技术进步带来的学习需求。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

mysql报错:

[Err] 1055 - Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘库名.表名.字段’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

分析

发生这个错误,一般是sql不规范,出现类似下列写法导致的:
select a字段,b字段 from 表 group by b字段

可以看到,明明按照b字段分组的,却想查出a字段,当然,mysql5.7.5版本之前,由于没有对这种做严格校验,所以如果刚好每组b字段都有相同的a字段,也是能查出来的,所以给了很多人一个错觉,就是觉得这样写也是没问题的,包括我之前也是这样认为的,汗颜。

但今天发现这个问题,却发现网友给出的解决方案,更是粗糙的不行。
一般网上搜到的解决方案是:

不启用ONLY_FULL_GROUP_BY,也就是不检测这种功能依赖关系,然后让你执行这个语句:
select @@global.sql_mode;
查出有ONLY_FULL_GROUP_BY,然后让你把ONLY_FULL_GROUP_BY删掉,再把剩下的值set进去,比如:
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
更有甚者,直接让你set一个空,合着把所有的检测这种都给关了:
set @@global.sql_mode=''; 
比如这个文章就是这么给的解决方案(这个还稍微好点,不是直接set空的):
https://blog.csdn.net/qq_34707744/article/details/78031413(并不是针对这个,相同解决方案的文章很多,也不知道谁抄的谁)

这样搞问题确实也解决了,但治标不治本。

正确的解决方案,应该是优化你的sql,因为sql规范中确实明确指出,用了group by后,select后的字段,只能是group by的字段,或者一些聚集函数,比如sum(a字段)这种。
查看sql92规范中,人家确实也明确指出针对group by的规范,如下:
sql相关group by规范的链接:http://dev.cs.ovgu.de/db/sybase9/help/dbugen9/00000284.htm
截图如下:
在这里插入图片描述

大概意思是:

用于GROUP BYSQL / 92标准要求满足以下条件:
	SELECT子句的表达式中使用的列必须在GROUP BY子句中。否则,使用该列的表达式是一个聚合函数。
	GROUP BY表达式只能包含选择列表中的列名,而不能仅用作向量聚合的参数。

解决

所以,针对上面这个不规范sql,应该改成:
select a字段,b字段 from 表 group by a字段,b字段;
而且最好ab字段是索引,当然这是group by的优化部分了,不多说了。

反思

这个问题,说实话我以前也没太注意,确实也是之前用的都是mysql5.7.5版本之前的低版本,也一直不报错,所以慢慢的惯出坏习惯了,不认为这是个问题。其实这个问题,也是我的一个很有资历的同事给我指出的,在此感谢他。
最近由于刚刚换了mysql8高版本版本,也是各种被教育,但我觉得是好事,版本在升级,人也要跟着进步。
绝对不能学习很多网友的那个解决方案,人家高版本专门把这个检测功能依赖关系加上了,你再手动关掉,那还不如直接用低版本算了。

不过网友的这种想法,有时候想想也能理解一点。因为在软件行业,干久了就会有一个思维,就是如果一个问题没办法正面解决,那就想办法绕过他,只要最终目的达到就行了。
比如高并发的这些问题,io的这些问题,确实因为现有技术原因解决不了,所以我们都会选择绕过正面去解决,比如通过加机器来提高系统并发等。
所以说,有这种绕过的思维是好的,但不能养成稍微一不会就想着怎么绕过,我们还是要对技术有最基本的探索精神的。

### 解决 MySQLGROUP BY 子句错误的方法 在 MySQL 5.7.5 及更高版本中,默认启用了 `ONLY_FULL_GROUP_BY` SQL 模式。这使得某些不符合标准的 SQL 查询会触发错误,特别是当查询中的选择列表包含未聚合列且这些列不在 `GROUP BY` 子句中时[^4]。 #### 方法一:调整 SQL_MODE 设置 可以通过修改服务器配置来禁用 `ONLY_FULL_GROUP_BY`: 1. 查看当前 SQL 模式的设置: ```sql SELECT VERSION(), @@sql_mode; ``` 2. 修改全局 SQL 模式并移除 `ONLY_FULL_GROUP_BY`: ```sql SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ``` 3. 验证更改是否生效: ```sql SELECT VERSION(), @@GLOBAL.sql_mode; ``` 这种方法适用于希望快速解决问题而不改变现有查询逻辑的情况。然而需要注意的是,这样做可能会引入潜在的数据不确定性风险,因此建议仅作为临时解决方案使用。 #### 方法二:修正查询语句 更推荐的做法是对原始查询进行优化,使其符合严格的分组规则。具体来说,在 `SELECT` 列表里只保留那些已经在 `GROUP BY` 后面指定过的字段或者是通过聚集函数处理后的结果。例如: 假设有一个如下所示会产生上述错误的信息检索请求: ```sql SELECT a.id, b.name FROM table_a AS a JOIN table_b AS b ON a.bid=b.id WHERE ... ``` 可以将其改为: ```sql SELECT MAX(a.id), b.name -- 使用聚集函数确保唯一性 FROM table_a AS a JOIN table_b AS b ON a.bid=b.id WHERE ... GROUP BY b.name; -- 将所有非聚集项加入到 GROUP BY 中 ``` 或者如果确实需要获取多个记录,则应考虑重构业务需求或采用其他方式实现相同目的而不会违反 `ONLY_FULL_GROUP_BY` 的约束条件。 #### 方法三:利用窗口函数 (Window Functions) 对于复杂场景下仍需保持原有输出结构的情形,可尝试应用窗口函数代替传统的 `GROUP BY` 结构。这样既能够满足严格模式下的语法要求又不影响最终呈现给用户的视图效果。 例如,要计算每个部门员工数量的同时显示每位成员的名字,可以用如下形式书写: ```sql WITH RankedEmployees AS ( SELECT e.*, ROW_NUMBER() OVER(PARTITION BY d.dept_id ORDER BY e.emp_name) as rn FROM employees e INNER JOIN departments d ON e.department=d.dept_id ) SELECT * FROM RankedEmployees WHERE rn = 1; ``` 此方法不仅解决了兼容性问题还提高了查询效率与灵活性。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值