mysql5.7使用groupby出现1055Expression #2 of SELECT list is not in GROUP BY clause 错误

最新推荐文章于 2024-09-09 10:22:25 发布
最新推荐文章于 2024-09-09 10:22:25 发布
阅读量1.6k 收藏 1
点赞数 1
分类专栏： # MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013541411/article/details/104212970
版权

今天想实现一个查看某一个类别的其中一项记录时，使用groupby分组，在mysql5.7出现，如下错误

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

在mysql中使用select @@sql_mode;查询：可以看到有ONLY_FULL_GROUP_BY

select @@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

解决方法：

第一步：在mysql命令行下执行以下语句： 

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

第二步：在mysql的配置文件中（linux下/etc/my.cnf或者/etc/mysql/my.cnf的[mysqld]下加入以下内容：

[mysqld]  
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服务

[root@hadoop01 mysql-5.7]# systemctl restart mysqld.service

再次查询

select @@sql_mode;

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

Crazer.
关注
专栏目录
出现User 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 解决方法
码农研究僧的博客
04-24 1620
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
DN金猿的博客
06-28 294
经过查询资料发现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，去掉即可。
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregate
hdyebd的博客
04-08 4704
在mysql 5.7初次使用group by时会出现错误,首先表信息如下:        ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.score...
MySql报错only_full_group_by的解决办法
最新发布
qq_37639246的博客
09-09 587
前段时间我在一个新环境里部署程序时遇到MySql报错only_full_group_by，之前已经遇到过一次同样的问题，当时没有总结经验，导致这次解决时耗费了不少时间，这里把本次的处理过程进行记录总结，同时分享给大家MySql数据库报错如下，从字面意思理解是select后面查询的字段没有出现在group by中MySql在低版本（5.7.x以下）中允许select后面的非聚合列不出现在group by中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错。
MYSQL---之严格模式详解
weixin_45932995的博客
12-07 830
严格模式参数详解： sql_mode ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。简而言之，就是SELECT后面接的列必须被GROUP BY后面接的列所包含。如： select a,b from table group by a,b,c; (正确) se...
MySQL问题：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY
江南好
04-05 2565
问题描述：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘commodity.a.xsjg’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by my
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
MissWwg的博客
08-30 1万+
SQL92及更早版本不允许选择列表，HAVING条件或ORDER BY列表引用在GROUP BY子句中未命名的非分组列的查询。例如，在标准SQL92中，此查询是非法的，因为name选择列表中的未列出的列不会显示在GROUP BY： SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c...
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
09-09
如果`SELECT`列表中的列没有出现在`GROUP BY`子句中，且该列没有被聚合函数（如`SUM`, `AVG`, `MAX`, `MIN`等）处理，MySQL会抛出`Expression #1 of SELECT list is not in GROUP BY clause and contains ...
解决MySQL报出1055-Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated类型的错误
weixin_45775973的博客
07-29 4155
在Mysql（版本为8.0.25）中进行分组查询的时候，频繁报出如下的错误：
mysql 遇到此问题解决办法：Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggre
永远年轻
05-25 4366
问题出现原因： MySQL 5.7.5 及以上功能依赖检测功能，如果启用了 ONLY_FULL_GROUP_BY SQL 模式(默认情况下启用)，MySQL 将拒绝选择列表，Having 条件或 Order by 列表的查询引用在 Group By 字句中即未命名的非集合列，也不在功能上依赖于它们。 解决方法一【重启服务就会失效】： // 使用 sql 查询 select @@global.sql_mode // 查询出来的值为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
爱码者的博客
10-23 984
mysql版本 5.7 出现的问题： [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'community.tab_order_goods.goods_name' which is not functionally dependent on c...
解决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 日期,活跃度; 以上代码运行报错.
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
qq_43227967的博客
03-30 7950
错误信息： [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scp.m.manufacturer_id' 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
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 报错 1055 ：Expression #2 of SELECT list is not in GROUP BY clause
momomuabc的博客
10-18 1556
MySQL 报错 1055 ：Expression #2 of SELECT list is not in GROUP BY clause 解决方案
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
baidu_41931697的博客
06-27 528
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
[Mysql 报错] 解决MySQL：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nona
汤圆的博客
09-30 1843
原因: MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BYSQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL5.6参考手册”。） 先查询配置信息: select @@global
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;...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值