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

最新推荐文章于 2024-11-01 11:24:29 发布

勇气在前

最新推荐文章于 2024-11-01 11:24:29 发布

阅读量2.8k

点赞数

分类专栏：错误信息文章标签： mysql 数据库 linux

本文链接：https://blog.youkuaiyun.com/weixin_44525558/article/details/106909573

版权

错误信息专栏收录该内容

1 篇文章

订阅专栏

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

原因：

MySQL 5.7.5及以上默认对功能依赖的检测。如果启用了only_full_group_by SQL模式，MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。

select @@global.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
执行以下命令：

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

勇气在前

关注关注

0
点赞
踩
1

收藏

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

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

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

DN金猿的博客

06-28

1055

经过查询资料发现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

2874

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 #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

qq_36627328的博客

04-16

779

原因出现此问题的原因是 MySQL 5.7.5 之后默认开启了 ONLY_FULL_GROUP_BY sql_mode。对此官方的解释：MySQL 实现了函数依赖性的检测。如果启用了 ONLY_FULL_GROUP_BY SQL模式（默认情况下是这样），MySQL 将拒绝 select list、HAVING condition 或 ORDER BY list 引用的未聚合列，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于它们。解决方法当然，最好的解决方法当然是修改优化 sql 语

Mysql问题Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

梦里看花

11-22

1904

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;

[mysql] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lo...

a84480766的博客

04-26

176

执行SQL: SELECT login_name,login_ip,sex FROM tank_admin GROUP BY login_name ; 时抛出异常。 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'loser.tank_admin....

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

qq_44137730的博客

12-16

495

使用Navicat按F6打开命令列界面输入：select @@global.sql_mode; 将其中的ONLY_FULL_GROUP_BY去掉输入： set @@global.sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

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

热门推荐

qq_39954916的博客

09-06

4万+

SELECT 日期,地域,活跃度,COUNT(qq号) as qq个数 FROM (SELECT a.`日期`,a.`qq号`,a.`地域`,IF(b.msg>=200,'高活跃度','低活跃度') AS 活跃度 FROM table_user AS a LEFT JOIN table_act AS b ON a.`日期` = b.ftime AND a.qq号 = b.qq WHERE a.`地域` IN ('广东','广东省'))t GROUP BY 日期,活跃度; 以上代码运行报错.

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

爱媳妇儿爱编程

11-01

1125

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

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

sahbnuymiy的博客

01-26

863

Expression #4 of 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

2087

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 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中的列没

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

baidu_41931697的博客

06-27

566

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

MySQL问题解决：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

whale的博客

01-08

3941

MySQL问题解决：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 问题还原：某次系统数据库迁移，配置了新的服务器作为mysql数据源，数据和原来的数据库保持一致；结果：项目部署成功后group by 相关的sql语句报错，见标题。原因： MySQL 配置中 sql_mode 默认配置了only_full_group_by，这样的话需要 GROUP BY 中包含所有在 SELECT 中出

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

Yeats_Liao的博客

04-23

3292

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

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

992宸的博客

05-15

313

在MySQL中输入 set sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’; 即可如果使用复制粘贴的话，请把两个单引号删了重新打，不然会报错如果手动输入则完全没有问题想了解更多详情可以加入公众号 ...

mysql group by 报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated c

wuxiaopengnihao1的博客

09-05

4131

这是MySQL8.0默认开启only_full_group_by模式导致的，该模式限定select后面跟的列，在group by后面必须要有。好了，以上是本文所有内容，希望对大家有所帮助，也希望大家对。多多支持，你们的支持是我创作的动力！：该方式重启mysql会失效。修改配置文件my.cnf。重启mysql即可。

mysql分组时出现Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre

weixin_45065907的博客

01-18

6188

在mysql里面执行命令 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

完美MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解