mysql常见报错之SELECT list is not in GROUP BY clause

最新推荐文章于 2025-03-04 15:47:21 发布
原创 最新推荐文章于 2025-03-04 15:47:21 发布 · 1.4k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql #数据库

当在MySQL 5.7及以上版本中使用GROUP BY时，如果启用了ONLY_FULL_GROUP_BY SQL模式，可能会遇到错误1055。此模式要求SELECT列表中的所有非聚合列都在GROUP BY子句中。解决方法是查询当前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'，即可消除错误。

开发中使用group by报错，错误信息： 1055:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

原来是因为mysql5.7 及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认情况下)，MySQL将拒绝选择列表

解决方法：

1、查询sql_mode    sql：select @@global.sql_mode;

2、去掉ONLY_FULL_GROUP_BY，重新设置值。

sql:  set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这样就可以了

mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 1327
经过查询资料发现group by 聚合操作的时候，进行查询操作的列没有在group by 中出现，那么这个sql就是不合法的。主要原因就是设置的与sql_mode=only_full_group_by不兼容。今天在mysql5.7.x 升级到8.0.x版本的时候 项目接口报错。通过查询发现mysql5.7.5及以上的版本都会依赖检测功能,也就是默认会开启only_full_group_by。8.0.x的版本不支持NO_AUTO_CREATE_USER，去掉即可。
【MySQL】报错1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated
weixin_61428498的博客
08-01 658
1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum完美解决！！！！！
MySQL SyntaxErrorException SELECT list is not in GROUP BY 报错解决
最新发布
C_V_Better的博客
03-04 850
错误是由于 MySQL 的模式引起的。通过修改 SQL 查询、修改 MySQL 配置或使用聚合函数，可以解决这个问题。在实际开发中，建议根据具体需求选择合适的解决方法。
MySQL：SELECT list is not in GROUP BY clause 报错 解决方案
m0_74823892的博客
12-27 1182
一大早上测试环境，发现测试环境的MySQL报错了。
MySQL报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法
热门推荐
lucky多多的博客
03-22 5万+
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
A乐神的博客
12-15 8578
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
09-09
如果`SELECT`列表中的列没有出现在`GROUP BY`子句中，且该列没有被聚合函数（如`SUM`, `AVG`, `MAX`, `MIN`等）处理，MySQL会抛出`Expression #1 of SELECT list is not in GROUP BY clause and contains ...
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
weixin_40925480的博客
11-03 1266
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic
weixin_63716356的博客
07-24 2205
MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic字面翻译： SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“grades.order_id” 它在功能上不依赖于 GROUP BY 子句中的列； 这与 sql_mode=only_full_group_by 不兼容使用GROUP BY 语句违背了 sql_mode=only_
【MYSQL】SELECT list is not in GROUP BY clause: mysql数据库异常解决方案
dkz97的博客
06-29 6732
SELECT list is not in GROUP BY clause: mysql数据库异常解决方案前言问题及需求解决方案合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 前言 你好！ 这是你第一次使用 Markdown编辑器 所展示的
division by zero_详解sql_mode参数ONLY_FULL_GROUP_BY导致的一个mysql错误
weixin_39605414的博客
11-26 1155
概述记一次最近碰到的一个mysql报错，还是比较少见的。1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.STATISTICS.NON_UNIQUE' which is not functionally depende...
sql报错SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_40262372的博客
06-24 9998
高频面经汇总：https://blog.youkuaiyun.com/qq_40262372/article/details/116075528 一、问题分析 其中错误的原因是说group by哪一句中的select的字段是不对的。 二、情景还原 dept_emp表： salaries表： 我想去查询每个部门人员薪水，于是写下了这样的sql: select de.dept_no ,s.salary from dept_emp de left join salaries s on..
SELECT list is not in GROUP BY clause and contains nonaggregated column解决和总结
ITxiaofeixiang的博客
04-12 4万+
问题描述 查询语句 SELECT name,sum(class_id) from res GROUP BY class_id 结果 SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dt.res.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=o
MySQL - MySQL使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column..
MinggeQingchun的博客
12-15 2164
要么禁用ONLY_FULL_GROUP_BY(脚本如下)2、对于用到 GROUP BY 的 select 语句, 查出来的列必须是 group by 后面声明的列, 或者是聚合函数里面的列有这样一个数据库的表。3、group by role (非主键) 的时候, select 只能是聚合函数和 role ( group by 的字段) , 否则报错。1、只要有聚合函数 sum()，count()，max()，avg() 等函数就需要用到 group by , 否则就会报上面的错误。
【已解决】SELECT list is not in GROUP BY clause and contains nonaggregated column
nanke_yh的博客
10-14 1192
一、原因 MySQL5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。 二、解决办法 1、命令行打开mysql.cnf,默认路径为/etc/mysql/conf.d/mysql.cnf,如果找不到可以使用whereis进行查询 sudo vim /etc/mysql/conf.d/mysql.cnf 按i 进行编辑 2、滚动到文件底部复制并粘贴以下代码 [mysqld] sql_mode = STRICT_TRANS_TABL
报错 SELECT list is not in GROUP BY clause
灵台方寸山斜月三星洞
08-17 1万+
[Err] 1055 - Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible wit...
SELECT list is not in GROUP BY clause...
一起学习前端知识，一起成为前端开发工程师！
04-21 286
线上服务器数据库查询使用了 GROUP BY 居然报出了 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'csc_risk.a.DefaultDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=onl
MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”
Michael Hsu
12-12 2万+
MySQL报错如下： Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shaogang.role_menu.role_menu_id' which is not functionally dependent on columns in GROUP BY clause...
MySQL报错:SELECT list is not in GROUP BY clause and contains nonaggregated column,解决ONLY_FULL_GROUP_BY
myyw001的博客
09-11 1677
3.去掉ONLY_FULL_GROUP_BY ，其他参数不变，执行即可。2.查看返回信息是否包含ONLY_FULL_GROUP_BY。1.查看.sql_mode配置。
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值