mysql数据库出现Expression #3 of SELECT list is not in GROUP BY clause

最新推荐文章于 2023-11-08 15:55:26 发布

风亦未止

最新推荐文章于 2023-11-08 15:55:26 发布

阅读量494

点赞数 2

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

本文链接：https://blog.csdn.net/adminzzk/article/details/129163953

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL5.7及以上版本开启ONLY_FULL_GROUP_BY模式，导致GROUPBY查询时需包含所有SELECT列或使用聚合函数。为消除此限制，可在my.cnf配置文件中修改sql_mode，或在阿里云数据库中调整SQL_MODE参数。

摘要由CSDN通过智能技术生成

mysql数据库出现：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxxx’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by解决方法：

mysql5.7以上都会默认启用 ONLY_FULL_GROUP_BY 的特性，对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

解决

在my.cnf文件追加：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如果用的阿里云数据库则修改sql_mode参数：

删除ONLY_FULL_GROUP_BY ，提交

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

风亦未止

关注关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

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

DN金猿的博客

06-28

230

经过查询资料发现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，去掉即可。

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

最新发布

码农研究僧的博客

04-24

1419

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

参与评论您还未登录，请先登录后发表或查看评论

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

weixin_43802097的博客

05-12

1272

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

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

qq_23157491的博客

02-24

571

解决方法：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';

mysql5.7以上报错：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

hhq850741642的博客

09-29

788

在写sql时报错信息：1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘pms.ru.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byselect @@glo

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

weixin_65549694的博客

06-20

1769

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

mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题

weixin_44306683的博客

08-07

292

mysql 5.7.x 版本遇见only_full_group_by 问题解决汇总 1 出错原因： MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们具体报错如下 select book.category AS category,book.categoryText AS categoryT

MySQL查询的时候出现 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre报错

weixin_43140656的博客

11-28

665

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre …后面一大堆出错就是这样我们可以在网上看到很多解释 MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查...

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 #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法

m0_52789121的博客

06-07

1292

今天使用mysql简单的一句分组查询语句（GROUP BY）报错了。查了问题原因和解决方法，成功解决。原因：使用GROUP BY 语句违背了sql_mode=only_full_group_by。因为mysql版本5.7之后默认的模式是ONLY_FULL_GROUP_BY。官网文档的原话：说人话，大概意思就是说（使用google翻译）：1、先使用SQL查询sql_mode 2、重新设置sql_mode，删除ONLY_FULL_GROUP_BY 3、退出重进再次执行查询语句，...

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre

m0_68064743的博客

04-06

3110

错内容 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre 原因分析 mysql 5.7默认启用ONLY_FULL_GROUP_BY特性，即：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。解决方法在mysql的

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

潘顾昌的博客

05-10

2015

1.场景 2020-05-09 17:54:23.297 WARN 1 --- [ XNIO-3 task-10] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

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

weixin_46715521的博客

11-08

256

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

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

weixin_43822858的博客

03-05

288

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

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

Ariel_欢

05-15

6283

1.问题描述：今天在写SQL语句时，需要将数据先按用户分组汇总，再按物品类型（测试数据库中字段是item_name）进行分组汇总，SQL语句中使用了group by分组函数，然后执行SQL报错：执行的SQL语句： SELECT a.user_id,a.item_name,a.item_id,a.approver_id,a.create_time,a.supposed_return_time FROM `t_approval` as a WHERE now() > a.supposed

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

天不拘兮地不羁

07-08

5381

“Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘workflow.s.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with s...

运行报错Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column xx....

爱码士的博客

02-01

1521

安装了mysql5.7，用group by 查询时抛出如下异常： Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘userinfo.t_long.user_name’ which is not functionally dependent on columns in ...