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

已于 2023-11-16 14:36:56 修改
阅读量399 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： mysql
于 2021-04-16 11:02:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36627328/article/details/115749654
版权

原因

出现此问题的原因是 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 语句，使得满足这个函数依赖性。如果代码太多或者你根本就不想这么做，解决方法无非就是不启用 ONLY_FULL_GROUP_BY SQL模式。

首先，查询目前启用的sql_mode：select @@global.sql_mode;

最低0.47元/天 解锁文章
Lurry525
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL Expression 2 of SELECT list is not in GROUP BY clause and contains nonaggregated column yous
wuekun的博客
05-28 376
MySQL:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘yousi.a.lon’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 执行的SQL SELECT a.box_no,
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
12-15
如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测...
mysql遇到Expression.docx
03-05
mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘learn_mysq
最新发布
Chrisliuluo的博客
03-27 382
报错原因：mysql的5.7以上版本都默认sql_mode=only_full_group_by，这个规则实际是为了提升查询的性能，因此规定我们在查询的时候只能准确的查找要查的唯一数据，不支持group by的分组条件与select的条件不一致。只能写 select id from tableA group id having id='*6*';这种语句，可以在having中控制模糊匹配，但是查询条件和分组条件必须一致。永久性解决该问题，只需要修改mysql的配置文件，my.cnf文件，添加一行配置。
MYSQL升级后GROUP BY语句出错解决方法
milletluo的博客
08-20 2万+
使用select cid,cname,count(fname) from foodorder.orderlist group by cid报错解决
SQL 错误 [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggreg
quanqxj的博客
01-15 6691
在使用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...
MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法
m0_67882861的博客
11-07 3109
因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）现有两表Student表以及SC表(选课表)。student表结构。
mysql group by 报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated c
wuxiaopengnihao1的博客
09-05 3985
这是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 nonaggregated column
皇位无忧的工作室
12-03 449
网站之前好好的，今天突然发现出现了报错，细看是数据库的报错，说是不好的语法什么的 其中重点的错误描述是：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column…… 于是上网查找了原因，发现是MySQL如果是5.7.5版本以上，就回对Group by的语法有检测和限制，至于具体原因我也看不懂： MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY
MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…
12-14
[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP ...
MySQL使用中遇到的问题记录
12-15
#1 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by 解决办法 # 设置正确的 sql_mode 即可解决 # 登陆MySQL ...
Mysql问题Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
梦里看花
11-22 1513
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 nonaggre
Smilelfq的专栏
01-28 850
报错： Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wlhy.c.com mysql分组时出现Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre 在mysql里面执行命令 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONL
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregate
weixin_44957344的博客
01-08 1069
执行sql语句报错： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.fruits.f_id’ which is not functionally dependent on columns in GROUP BY cl...
报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
Ai_Answer_zr的博客
01-02 477
2023-4-26：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 'sss
热门推荐
蚁人的博客
03-21 2万+
执行MySQL中sql语句的时候报错，执行的sql语句：SELECT prov_desc, area_desc, month_id, MAX(total_fee)AS max_total, FROM sss WHERE prov_id = '075' OR prov_id IN('017') AND month_id IN('201207') GROU
SELECT list is not in GROUP BY clause and contains nonaggregated column
J_bean的博客
03-07 7635
报错 MySQL 8.0.22 GROUP BY 语句执行报错： 1055 - Expression #22 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.xxx.xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_fu
Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated ...
qq_59025975的博客
09-19 4954
在Mysql版本为5.7.25时，在使用使用group by 时，会出现Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated...错误,这个错误的原因是 group by后面需要加上，select中的所有字段。不然就会报这个错误。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值