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

最新推荐文章于 2023-11-07 22:40:00 发布
最新推荐文章于 2023-11-07 22:40:00 发布
阅读量1.5k 收藏 3
点赞数
分类专栏： 问题解决 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48842132/article/details/114385550
版权

1.问题原因

MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一，导致了一些sql语句的失效。

2.解决方法

  1. 打开navicat，点击新建查询

    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

    再执行一条sql语句：

    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'

    如果是mysql8及以上请把NO_AUTO_CREATE_USER删去
    原因：MySQL8取消了NO_AUTO_CREATE_USER
    即

    set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

  2. 修改mysql.cnf文件

    1. 切换到对应用户下执行sudo vim /etc/mysql/conf.d/mysql.cnf
    2. 然后输入 i 将模式变为insert
      sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 复制到文件末尾
    3. 按键盘Esc退出insert模式
    4. 最后输入:wq 保存退出
    5. 重启msyql: sudo service mysql restart
小陌白
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
mysql报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
DN金猿的博客
06-28 114
经过查询资料发现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，去掉即可。
mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题
weixin_44306683的博客
08-07 280
mysql 5.7.x 版本 遇见only_full_group_by 问题解决汇总 1 出错原因： MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们 具体报错如下 select book.category AS category,book.categoryText AS categoryT
MySQL使用中遇到的问题记录
12-15
这儿记录一些在使用MySQL的过程中遇到的问题以及相应的解决办法。 sql_mode=only_full_group_by引起group by查询报错 问题 MySQL执行 GROUP BY 查询报错： #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 sudo mysql
mysql遇见Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre问题解决
axiaobaoa的博客
01-06 1万+
解决执行sql语句报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' 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 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中的列没
MySQL查询的时候出现 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre报错
weixin_43140656的博客
11-28 629
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre …后面一大堆 出错就是这样 我们可以在网上看到很多解释 MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查...
MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…
12-14
在MySQL中，错误提示 "[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column…” 指的是在执行包含`GROUP BY`子句的查询时，你试图对未在`GROUP BY`子句中...
解决大于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中，当你尝试执行一个包含`GROUP BY`子句的查询时，可能会遇到"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"这样的错误。这个错误提示表明你的查询违反了`...
SQL 错误 [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggreg
quanqxj的博客
01-15 6975
在使用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异常处理
Yeats_Liao的博客
04-23 2731
MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emplo
weixin_43256057的博客
12-20 612
情况：输入以下命令 select group_concat(name),salary from employee left join department on employee.departmentID=department.d_id group by department.d_id; mysql里一旦报这个错：select group_concat(name),salary from empl...
MySQL报错 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column解决方法
最新发布
m0_67882861的博客
11-07 3543
因为sno属性是主码，即primary key，经测试发现group by **，**为主码的属性名时，select语句中的查询列没有在group by中出现，也是允许的。红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中，但执行语句依旧正常，因为使用了聚集函数。执行mysql语句，select 选择的列不仅仅只有sno，为什么正常输出？存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）现有两表Student表以及SC表(选课表)。student表结构。
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
weixin_45626288的博客
03-29 3053
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wsq.m.chat_room_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql group by 报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated c
wuxiaopengnihao1的博客
09-05 4048
这是MySQL8.0默认开启only_full_group_by模式导致的，该模式限定select后面跟的列，在group by后面必须要有。好了， 以上是本文所有内容，希望对大家有所帮助，也希望大家对。多多支持，你们的支持是我创作的动力！：该方式重启mysql会失效。修改配置文件my.cnf。重启mysql即可。
mysql使用GROUP BY分组时报错：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggre
weixin_43136158的博客
08-02 1305
原因 MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL5.6参考手册”。） 解决办法 在Navicat for mysql软件的Mysql命
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
09-26
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.order_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible ...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

小陌白

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

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

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

打赏作者

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

抵扣说明：

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

余额充值