ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregate

最新推荐文章于 2024-12-05 09:03:18 发布

原创最新推荐文章于 2024-12-05 09:03:18 发布 · 4.8k 阅读

2 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

9 篇文章

订阅专栏

本文介绍在MySQL5.7中使用Group By时遇到的ERROR1055问题及解决方案。通过调整my.cnf或my.ini配置文件中的sql_mode设置来避免ONLY_FULL_GROUP_BY模式引发的错误。

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

在mysql 5.7初次使用group by时会出现错误,首先表信息如下:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.score.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select c_name,id from score group by c_name;

在这里插入图片描述
查看一下MySQL5.7的检查模式

在这里插入图片描述

通过修改my.cnf或者my.ini配置文件去掉ONLY_FULL_GROUP_BY一项(如果没有这个配置项则新建)
在文件里加上下面一行

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
此时就不会在出现ONLY_FULL_GROUP_BY
在这里插入图片描述

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

hdyebd

关注关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

出现User 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 解决方法

码农研究僧的博客

04-24

2947

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'easy-admin.students.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

DN金猿的博客

06-28

1148

经过查询资料发现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---之严格模式详解

weixin_45932995的博客

12-07

995

严格模式参数详解： sql_mode ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。简而言之，就是SELECT后面接的列必须被GROUP BY后面接的列所包含。如： select a,b from table group by a,b,c; (正确) se...

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

MissWwg的博客

08-30

1万+

SQL92及更早版本不允许选择列表，HAVING条件或ORDER BY列表引用在GROUP BY子句中未命名的非分组列的查询。例如，在标准SQL92中，此查询是非法的，因为name选择列表中的未列出的列不会显示在GROUP BY： SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c...

[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

yangyongming_888的博客

03-09

8020

[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'epms.lk.item_fid' which is not functionally dependent on columns in GROUP BY clause; this is inco...

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

河静

11-12

465

mysql表结构如下： mysql> select * from fruits ; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 |

MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…

12-14

在MySQL中，错误提示 "[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column…” 指的是在执行包含`GROUP BY`子句的查询时，你试图对未在`GROUP BY`子句中...

mysql版本升级出现Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column错

爱媳妇儿爱编程

11-01

1170

mysql 升级版本出现experssion #2 ONLY_FULL_GROUP_BY错误问题解决

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘rea

weixin_43798579的博客

09-28

1808

mysql报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘realname.t_act_coding1.nin_coding’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_grou

MySQL问题：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY

江南好

04-05

2681

问题描述：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘commodity.a.xsjg’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by my

报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

爱码者的博客

10-23

1029

mysql版本 5.7 出现的问题： [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'community.tab_order_goods.goods_name' which is not functionally dependent on c...

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

qq_43227967的博客

03-30

8012

错误信息： [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scp.m.manufacturer_id' which is not functionally dependent on columns in GROUP BY clause;...

解决Mysql：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated