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

moonlight_Answer

于 2024-01-02 11:12:47 发布

阅读量779

点赞数 9

分类专栏： work_learn 文章标签： mysql

本文链接：https://blog.csdn.net/Ai_Answer_zr/article/details/135336051

版权

work_learn 专栏收录该内容

21 篇文章 2 订阅

订阅专栏

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
原因：

MySQL版本5.7之后会检测函数依赖，默认启用的模式是ONLY_FULL_GROUP_BY，使用GROUP BY 语句违背了sql_mode=only_full_group_by。该模式的意思是只有确定唯一字段的group by才能执行
select 查询的字段没有在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';

修改my.cnf配置文件里面的sql_mode参数值，并重启mysqld服务

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

moonlight_Answer

关注关注

9
点赞
踩
6

收藏

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

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

MySQL5.7中的sql_mode默认值带来的坑及解决方法

12-15

在正常项目开发过程中，如果MySQL版本从5.6升级到5.7版本。作为DBA在考虑数据库版本升级带来的影响时，一般会有几个注意点： sql_mode optimizer_switch 本文主要内容是MySQL升级到5.7版本之后，由于默认的 sql_mode 值带来的坑以及对应的解决方案。案例一：ONLY_FULL_GROUP_BY 问题描述 MySQL版本从5.6升级至5.7之后，部分SQL执行报错，报错信息如下： ERROR 1055 (42000): Expression #3 of XXXXXX list is not in GROUP BY clause and contain

mysql出现Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre错误

weixin_45032599的博客

01-13

8258

1、问题原因 mysql为5.7之前，则会默认启用ONLY_FULL_GROUP_BY SQL模式，MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。 2、解决方法 1、第三方视图工具（Navicat） select @@global.sql_mode 使用sql查询出结果后，一般情况会出现ONLY_FUL...

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

mysql遇见Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre问题解决

axiaobaoa的博客

01-06

1万+

解决执行sql语句报错Expression #2 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 with sql_mode=only_full_group_by

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

Orient的博客

10-21

1万+

一，问题现象在mysql中，用group by 分组时报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre，运行的sql select a.id, a.name, a.age from a group by id, name 这个主要是违背了mysql开启的校验only_full_group_by 检测二，问题的本质这个问题主要是mysql 开启了onl...

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

热门推荐

Hi_alan的博客

07-19

1万+

Expression #2 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 查阅资料发现，对于group by聚合操作,如果在select中的列没

SQL 错误 [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggreg

quanqxj的博客

01-15

7309

在使用group by时，报错信息如下： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mome.a.type' which is not functionally dependent on columns in GROUP...

解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

09-09

且该列没有被聚合函数（如`SUM`, `AVG`, `MAX`, `MIN`等）处理，MySQL会抛出`Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated`错误。这个问题在5.7版本之前并不严格检查，但在...

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.docx

03-05

在MySQL中，当你尝试执行一个包含`GROUP BY`子句的查询时，可能会遇到"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"这样的错误。这个错误提示表明你的查询违反了`...

MySQL使用中遇到的问题记录

12-15

当MySQL执行GROUP BY查询时，如果SELECT列表中包含未在GROUP BY中指定的非聚合列，且该列未包含在聚合函数（如SUM、AVG等）中，就会报错。解决这个问题的方法是调整`sql_mode`的设置。你可以通过登录MySQL并运行以下...

解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

12-16

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘col_user_6.a.START_TIME’ which is not functionally dependent on columns in GROUP BY clause; this is ...

mysql不支持group by的解决方法小结

12-14

在`ONLY_FULL_GROUP_BY`模式下，所有出现在`SELECT`列表中但未包含在`GROUP BY`子句的列必须是聚合函数的一部分，或者与`GROUP BY`列有函数依赖关系，否则会引发错误。当`ONLY_FULL_GROUP_BY`启用时，如果你的查询...

MySQL报错1093 – You can’t specify target table ‘t’ for update in FROM clause, Time: 0

12-14

在MySQL数据库中，错误代码1093 - "You can’t specify target table ‘t’ for update in FROM clause" 是一个常见的错误，它通常发生在尝试在一个`UPDATE`语句的`FROM`子句中直接引用要更新的目标表时。...

Mysql升级到5.7后遇到的group by查询问题解决

12-16

发现问题最近在将mysql升级到mysql 5.7后，进行一些group by...SELECT list is not in GROUP BY clause and contains nonaggregated column ‘news.id' which is not functionally dependent on columns in GROUP BY c

mysql中You can’t specify target table for update in FROM clause错误解决方法

09-10

WHERE id IN (SELECT id FROM table_name WHERE some_condition GROUP BY column HAVING count(*) > 1); ``` 这两种情况都试图在`FROM`子句中引用目标表`table_name`，这是MySQL不支持的。解决这个错误的方法通常...

MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理

Yeats_Liao的博客

04-23

2885

MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emplo

weixin_43256057的博客

12-20

645

情况：输入以下命令 select group_concat(name),salary from employee left join department on employee.departmentID=department.d_id group by department.d_id; mysql里一旦报这个错：select group_concat(name),salary from empl...

MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法

m0_67882861的博客

11-07

4107

因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）现有两表Student表以及SC表(选课表)。student表结构。

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