Mysql——SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

最新推荐文章于 2024-06-20 09:59:06 发布
最新推荐文章于 2024-06-20 09:59:06 发布
阅读量1k 收藏 1
点赞数
分类专栏： MySql 文章标签： 数据库 MySql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36125181/article/details/101646419
版权

最近使用linux版本的Mysql的时候出现了错误：

SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

最后发现是因为版本问题，在Mysql5.*之后的版本sql_mode缺省值为ONLY_FULL_GROUP_BY，这也是需要修改的地方。
only_full_group_by ：使用这个就是使用和oracle一样的group 规则,

select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行

其实这个配置目前个人感觉和distinct差不多的，所以去掉就好 。

进入数据库，输入SHOW SESSION VARIABLES; 命令行 ，找到sql_mode行，查看是否为ONLY_FULL_GROUP_BY
在这里插入图片描述
如果是的话，接下来输入
set session sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
即可修改！修改之后的值为下图所示:
在这里插入图片描述
还有一个属性要修改 SHOW GLOBAL VARIABLES; 其操作步骤与 2 丝毫不差 , 可照搬操作

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’;

修改完成之后，再次查询即可
在这里插入图片描述

参考了网上的一些解决办法！！！

donne.go
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql 8.0.21 “SELECT list is not in GROUP BY clause and contains nonaggregated column...“错误解决方案
TuTuTu_Tu的博客
09-21 839
今天用EF框架生成Mysql实体模型时遇到了 Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘STATISTICS.INDEX_TYPE’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
weixin_40925480的博客
11-03 1092
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.
mysql中SELECT list is not in GROUP BY clause and contains nonaggregated column解决办法
weixin_45810161的博客
01-02 2327
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
MySQL：SELECT list is not in GROUP BY clause 报错 解决方案
最新发布
换条路吧，编程没意思的~
06-20 2137
MySQL 报错：SELECT list is not in GROUP BY clause and contains nonaggregated column 解决方案
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
A乐神的博客
12-15 7361
mysql 提示SELECT list is not in GROUP BY clause and contains nonaggregated column whic
mysql SELECT list is not in GROUP BY clause and contains nonaggregated column
opfan的博客
11-05 682
安装了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 i...
[MySQL]SELECT list is not in GROUP BY clause and contains nonaggregated column...
weixin_43702272的博客
12-20 1352
项目在windows上运行正常，迁移到Linux系统上就出现了这个错，经查是由于本人sql不规范且恰好mysql的sql_mode启用了only_full_group_by，当sql语句中使用了分组查询，并且查询的字段不在group by子句中就会出现这个报错提示。 报错详情：(sql版本8.0.13) SELECT list is not in GROUP BY clause and con...
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column...
weixin_43697366的博客
06-08 623
今天接口突然返回这个错误： 因为一直使用的是阿里云 rds实例，之前接口返回正常，没有更新过代码，因为最近重新购买了该实例rds,所以问题定位到这个上边，后来查询是mysql的配置是默认了参数的sql_mode=only_full_group_by MySql在低版本（5.7.x以下）中允许select后面的非聚合列不出现在group by中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错 通过该命令查询：SELECT @@sql_mode 解决方法就是在对应rds实例化的.
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column...原因及解决方案
江海寄余生的博客
03-30 1万+
最近公司新建了一个项目，在项目中需要用到group by进行聚合计算，在计算的同时也要查出一些其他字段来返回给前端。于是就有了这个错误的出现。 先简单复现我所写的sql，其实sql非常简单。 select channel_name as channelName, brand_name as brandName, sum(actual_value) as actualValue, sum(actual_value_ty) a
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.countFan
weixin_43802097的博客
05-12 1263
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
SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_42334297的博客
09-10 8584
SELECT list is not in GROUP BY clause and contains nonaggregated column问题解决方案单次解决修改配置文件使用规范的sql语句 问题 在单独部署项目的时候遇到了这个问题，具体意思就是： SELECT列表不在GROUP BY子句中，且包含非聚合列 意思应该就是SELECT的属性必须出现在GROUP BY语句中，即类似于SELECT a.name FROM user a GROUP BY a.name这种样子。 问题根源应该是sql语句的问题，
【解决】 SELECT list is not in GROUP BY clause and contains nonaggregated column
丑小鸭
01-18 1万+
出错情况 问题分析 出现此异常的原因是：如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。 使用数据库的版本信息 mysql-8.0.15-winx64 解决策略 法 1 1 查看 sql_mode 1 使用以下命令查看自己的 sql_mode select @@global.sql_mode; 或者 show variables
mysql 8.0下的SELECT list is not in GROUP BY clause and contains nonaggregated column
aituochang1886的博客
08-27 1073
mysql的版本 mysql> select version();+-----------+| version() |+-----------+| 8.0.12 |+-----------+ 在执行group by时遇到报错，具体如下 mysql> select * from api_properties GROUP BY file_id order by fi...
解决MySql下SELECT list is not in GROUP BY clause and contains nonaggregated column (ONLY_FULL_GROUP_BY)
MrYang_Wei
05-18 829
一、问题描述 今天同事遇到了一个数据库问题，在此我来进行记录。我们先看下sql片段 分析下这段sql，我们在使用 group by 关键字进行查询时，select 下检索列理应只包含group by 下的字段及聚合函数，但奈何场景需要，有时又想加上其它字段，而且她这样写在本地是可以运行的！！！ 二、解决方案 首先，本地的MySQL数据库版本是5.5.x，而linux服务器下的数据库版本为5.7.x。其次，在本地执行这段sql是没有问题的，而在服务器上才会出现这个问题。显而易见，这是由于MySQL
SELECT list is not in GROUP BY clause and contains nonaggregated column解决和总结
热门推荐
ITxiaofeixiang的博客
04-12 4万+
问题描述 查询语句 SELECT name,sum(class_id) from res GROUP BY class_id 结果 SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dt.res.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=o
In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column...
0bug0warning的博客
12-05 1万+
maven项目报错error: expression #2 of SELECT list contains nonaggregated column 'newhelp.appr.title'; this is incompatible with sql_mode=only_full_group_by 这是因为随着MySQL版本更新带来的问题，在MySQL5.7版本之后对group by进行了优...
Mac下的mysql 8.0.19使用group by报错：SELECT list is not in GROUP BY clause and contains nonaggregated colum
zhpf225的博客
08-17 911
问题 mac下的使用sql语句中的group语句报错： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.user.user_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_
mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column
07-27
回答: 当使用GROUP BY语句进行查询时，SELECT语句中只能包含GROUP BY后面声明的列或者聚合函数。这是因为MySQL的ONLY_FULL_GROUP_BY模式要求SELECT语句中的列必须是明确的，以确保分组求最值的合法性检查。这种模式采用了与Oracle、DB2等数据库的处理方式。因此，如果在GROUP BY语句中使用了聚合函数，就需要将SELECT语句中的列限制在GROUP BY后面声明的列或者聚合函数中。如果SELECT语句中出现了不明确的列，就会报错"SELECT list is not in GROUP BY clause and contains nonaggregated column"。\[1\]\[2\]\[3\] #### 引用[.reference_title] - *1* [mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column.](https://blog.csdn.net/weixin_40925480/article/details/127680083)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [mysql使用group by查询报错SELECT list is not in GROUP BY clause and contains nonaggregated column......](https://blog.csdn.net/weixin_37557729/article/details/123896433)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值