【MySQL】报错1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

无Bug不java

已于 2024-02-28 11:11:21 修改

阅读量629

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-08-01 15:43:00 首次发布

本文链接：https://blog.csdn.net/weixin_61428498/article/details/132043474

本文介绍了如何在MySQL5.7及以上版本中检查和设置sql_mode，以避免错误。解决方案包括针对不同版本的设置说明，但指出重启后可能需要重新调整，并强调了直接修改SQL语句的重要性以避免生产环境问题。

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

1055 - expression #3 of select list is not in group by clause and contains nonaggregated colum

命令在终端或者navicat中输入执行均可

①查看MySQL版本，确认MySQL版本。

-- 查询mysql版本命令一：5.7.30
select version() from dual;
 
-- 查询mysql版本命令二：5.7.30
select @@version

②已经确认版本确实是5.7版本以上，那接下来就要确认一下当前的模式：

-- 查看mysql默认的模式方式一：
SELECT @@GLOBAL.sql_mode;
 
-- 查看mysql默认的模式方式二:
SELECT @@SESSION.sql_mode;

解决方法：

Mysql 版本小于 8

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 版本大于 8 去除 NO_AUTO_CREATE_USER

最低0.47元/天解锁文章

200万优质内容无限畅学

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

无Bug不java

关注关注

0
点赞
踩
0

收藏

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

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

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

码农研究僧的博客

04-24

2958

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

热门推荐

JaneYork的博客

02-17

1万+

1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fbjs.mscc.ContactTime' which is not functionally dependent on columns in GROUP BY clause; this is incom...

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

qq_42926722的博客

10-25

5348

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

Mysql1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated问题报错

weixin_43822858的博客

03-05

346

今天写sql分组查询的时候报了这么个错，网上查解决办法都是改 @@global.sql_mode。如：但是不知道什么原因(可能是因为我下载的是免安装版？)，我改了之后group分组还是报上面的错误。都准备放弃了，准备接着往后写，突然发现了问题所在==||；先说解决方法吧(其实也不算解决方法吧)，免得大家着急：) 在你按某字段分组的时候，其他字段每一列的值可能不同，比如下图所示的表：此时运行sql语句： SELECT id from test GROUP BY area; 就会报标题所示错误。

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

好幸运

10-28

415

原因：数据库版本问题，本地5.7.12-log，服务器5.7.27 解决方法： 1.找到数据库的my.ini文件（my.cnf） 2.在[mysqld] 字段下添加 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_...

MySQL： 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

weixin_44040505的博客

01-10

370

MySQL： 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 错误愿意大概是：SELECT 的字段列表没有包含在 GROUP BY 语句中很多年之前都用的好好的，怎么就报错了呢！！！！查了一下，哦，这又是 MySQL 5.7 的锅。优化代码，剔除 sel...

mysql报错115_mysql报错Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated ...

weixin_32340873的博客

01-30

946

今天sql一对多关联查询发现一个错误，提示说查询的字段不在group by的子句中，因为sql_mode是only_full_group_by。报错信息：#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xt_sc.t_comment.content' ...

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.countFan

weixin_43802097的博客

05-12

1353

mysql中的sql__@@mode问题，导致sql无法运行 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.countFangWenCiShuByToday' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column……报错的解决办法

weixin_65549694的博客

06-20

1958

问题原因：这个报错是执行有GROUP BY的语句时出现的,原因是MySQL启用了默认的only_full_group_by SQL模式,导致GROUP BY语句报错.解决办法：打开mysql的安装目录找到my.ini配置文件，添加下面这行代码：具体位置如下：然后，右击此电脑-->管理->服务->找到mysql服务，重启此服务，问题解决。...

异常Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决方法

weixin_46715521的博客

11-08

545

mysql语句执行报错的解决方法

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

qq_51853681的博客

12-20

741

今天学习MySQL分组查询遇到一个错误本人英语不行，只能上百度翻译虽然还是看不太懂，但大概是说，在group by子句后面缺少了job_id的字段，因为前面用到了聚合函数，所以选择的列必须是分组的列，所以只要在group by 子句后面加上字段就解决了 ...

mysql:Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

qq_23157491的博客

02-24

645

解决方法：set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

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

二十一克阳光！的博客

12-05

1042

在mysql中使用 group by 出现的问题 ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ynn.goods.goods_id’ which is not functionally dependent on column...

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

milayz的博客

06-02

601

在MySQL使用group by时会出现错误，：

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

08-25

引用:1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'senior_two.score.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 。引用:这个错误是由于 MySQL 的新版本中默认开启了ONLY_FULL_GROUP_BY模式，即在 GROUP BY 语句中的 SELECT 列表中，只能包含分组或聚合函数，不能包含其他列。而你的查询语句中出现了一个列senior_two.score.student_id，它既没有被分组也没有被聚合，因此 MySQL 报出了这个错误。。引用:如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测...。根据引用，这个错误意味着在SELECT语句中的第二个表达式不在GROUP BY子句中，并且包含了一个非聚合的列'senior_two.score.student_id'。这与sql_mode=only_full_group_by模式不兼容。根据引用，这个错误是由于MySQL的新版本中默认开启了ONLY_FULL_GROUP_BY模式。在GROUP BY语句的SELECT列表中，只能包含分组或聚合函数，不能包含其他列。而你的查询语句中出现了一个列'senior_two.score.student_id'，它既没有被分组也没有被聚合，因此MySQL报出了这个错误。如果你启用了only_full_group_by SQL模式（默认情况下是启用的），那么MySQL会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。123 #### 引用[.reference_title] - *1* *2* [1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘se](https://blog.csdn.net/m0_61615803/article/details/130076452)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *3* [解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains ...](https://download.csdn.net/download/weixin_38743076/13698627)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]