Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

最新推荐文章于 2024-09-08 19:42:24 发布
最新推荐文章于 2024-09-08 19:42:24 发布
阅读量317 收藏
点赞数 1
分类专栏： 个人笔记 java开发 mysql 文章标签： mysql group by sql-mode
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a309220728/article/details/82970097
版权
个人笔记 同时被 3 个专栏收录
22 篇文章 2 订阅
订阅专栏
java开发
22 篇文章 0 订阅
订阅专栏
mysql
2 篇文章 0 订阅
订阅专栏

原因：

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

解决：

找到mysql配置文件my.cnf 添加以下内容
[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
linux下输入service mysqld restart
在这里插入图片描述

进入mysql 输入

select @@sql_mode;
成功
如图显示则成功

如果不行，进入mysql 输入

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

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

然后重起，OK

IT界的奇葩
关注
专栏目录
mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 346
经过查询资料发现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 1804
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 574
原因 出现此问题的原因是 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 语
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
山鬼谣的专栏
05-06 6882
环境 MySQL：8+ 在执行类似这样的语句时： SELECT * from template_detail a group by template_id; 然后报错了： 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ssm.template_detail.id' which is not functionally dependent on columns
MySQL运行报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre解决方法
最新发布
weixin_46949892的博客
09-08 522
原因：在MySQL5.7.5版本之前，这类语句是默认可以执行的，因为默认没有开启ONLY_FULL_GROUP_BY，在此版本之后，MySQL默认开启了此模式，执行此类SQL就会报错了。查询当前MySQL所用的模式配置，拷贝查询结果，然后将其中的ONLY_FULL_GROUP_BY删除掉，然后复制删除后的结果到如下SQL中。查询当前MySQL所用的模式配置，拷贝查询结果，然后将其中的ONLY_FULL_GROUP_BY删除掉，然后复制删除后的结果到如下SQL中。修改MySQL的配置文件：my.ini。
遇见 mysql Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre 问题
Tz.的博客
07-08 4177
这是因为mysql5.7及以上版本的mysql中，对于 group by 的这种聚合操作，如果在select 中的列， 没有在group by 中出现，那么这个SQL是不合法的，因为列不在group by的从句中，所以对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX()，SUM()，ANT_VALUE()的这种聚合函数，才能完成GROUP BY 的聚合操作。 查询以后发现是因为only_full_group_by设置的原因，下边我们将其进行关闭： 1. 第一种的解决办法是.
mysql报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
lk的博客
01-19 8774
报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre 1.描述： 数据库中的表grades为 id | gro | name | score --------------------------- 1 | A | 张三 | 20 2 | B | 李四 | 19 3 | B | 王五 ...
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
和代码死磕到底的博客
04-19 1万+
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_F...
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.countFan
weixin_43802097的博客
05-12 1301
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
[Linux 完美解决]Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column
m0_48296376的博客
03-24 2167
错误 Expression #5 of 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 问题 另一个: mysql遇见Expression #1 of SELECT list is not in GROUP BY clau
sql_mode=only_full_group_by研读
热门推荐
Allen_Tsang的博客
02-06 2万+
MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。比如下表game： id group_id name score 1 A 小刚 20 2 B 小明 19 3 B 小花 17 4 C 小红 18 执行sql: select name, grou
[mysql] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lo...
a84480766的博客
04-26 157
执行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 #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column，
陌子曦的专栏
05-07 3755
完整报错如下：Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sa.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with s...
MYSQL报错 ：Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column
sahbnuymiy的博客
01-26 649
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
陈一一同学的博客
06-21 2753
MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zx_ds.biz_table_ver.ver_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n### ....
mysql:Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
qq_23157491的博客
02-24 595
解决方法：set @@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:Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DXIANGH的博客
06-10 1765
mysql only_full_group_by问题
MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
daponi的博客
09-23 5028
安装了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 in ...
mysql遇见 of SELECT list is not in GROUP BY clause and contains nonaggregated column
工作中学习，学习中进步
11-02 1651
安装了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 in GROUP BY clause; this is incompatible with sql_mode=only
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

IT界的奇葩

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值