1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ...

最新推荐文章于 2024-03-25 14:21:37 发布

转载最新推荐文章于 2024-03-25 14:21:37 发布 · 245 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/a9999/p/9777163.html

文章标签：

#数据库

本文介绍了解决MySQL中因ONLY_FULL_GROUP_BY导致的错误的两种方法。第一种是在不重启服务的情况下临时修改sql_mode参数，第二种是在my.cnf或my.ini文件中永久性地调整设置。文章详细解释了每种方法的优缺点。

解决方法一：

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

优点：不用重启mysql

缺点：重启mysql后还会出现标题错误

SELECT @@global.sql_mode;

查询全局的

@@session.sql_mode 当前回话的

上面的只是修改当前回话，需要修改全局的sql_mode

解决方法二

在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

注:sql_mode值用select @@sql_mode;查询出来后，将ONLY_FULL_GROUP_BY去掉剩余的值

优点：重启mysql 一次性解决问题

缺点：需要重启mysql

可以结合上述两种方案，来避免重启mysql服务，又能避免需要马上重启mysql

注：

my.cnf linux路径默认是/etc/my.cnf

在window下是my.ini

转载于:https://www.cnblogs.com/a9999/p/9777163.html

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

ajphsb0849

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

yemuyouhan的博客

12-10

9754

在用Navicat写MySQL命令时，出现以下错误： [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 depend...

MySQL报错1055解决办法：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains

澎湖Java架构师的博客

06-07

1348

时间：2021年12月20日14:44:04 周一临近寒假，各科考试接踵而至，笔者今天考试的科目是MySQL，拿到了老师给的准备考试的文档，在Navicat中复制粘贴了之后，执行后突然报错：记录一下解决办法：错误代码显示： [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ w

参与评论您还未登录，请先登录后发表或查看评论

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`子句中...

处理docker部署的mysql8在执行sql时报错[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause...

阿飞的博客

09-01

788

在配置mysql.cnf的[mysqld]内新增sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 可长期有效。安装apt install vim 提示没有apt。然后问题又来了，docker内没有常用编辑工具vi、vim。-i ：直接修改读取的文件内容，而不是输出到终端。可本次mysql运行解决，重启mysql失效。......

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

赵大白

07-11

693

完整错误### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'a.add_...

1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘apimonitor.t.cr...

jyjj_的博客

04-19

245

修改数据表 ENGINE = MyISAM 后，执行任何查询语句都会报告该错误，在试了一些博客的方法后解决；如下： sql版本 window mysql 8.0 修改mysql配置文件 my.ini，在最后一行添加 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 再重启mysql即可。 ...

MySQL操作报错：Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated colu

热门推荐

总结经验，与君共勉

12-05

1万+

在mysql5.7之后的版本运行sql语句时，出现以下问题： [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 de...

mysql ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

三人行，必有我师焉。

11-24

997

mysql的sql_mode设置

ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema

木鱼酱的博客

10-26

695

今天打开 navicat运行一个update语句时，出现已经update成功，但是会报如下错误：[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

ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROF

逗比爱好者

01-03

1254

ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROF：错误是从这里开始的，我百度了一下，有两种解决办法 1. 打开mysql查询命令输入 select version(), @@sql_mode;//这个是查看你配置的 SET sql_mod...

ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘xxx.sxxx_.id‘ which is

hello world

01-06

1388

异常信息 2022-1-6 09:05:28 [http-nio-8092-exec-8] WARN [SqlExceptionHelper]:137 SQL Error: 1055, SQLState: 42000 2022-1-6 09:05:28 [http-nio-8092-exec-8] ERROR [SqlExceptionHelper]:142 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains non

mysql5.7 group报错ERROR 1055 (42000): Expression #1

Marhal的博客

03-27

823

ubuntu16.04 apache2.4 php7.0 mysql5.7 $order_goods=db('order_goods')->where('orderid','in',$_orderid_arr)->group('orderid')->having('id>0')->select(); echo db('order_goods')->getL...

数据库报错： Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

weixin_46945048的博客

07-07

1459

数据库操作出错操作语句： TRUNCATE contract_ele; 错误信息： [SQL]TRUNCATE contract_ele; 受影响的行: 0 时间: 0.156s [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 functiona

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

09-15

这是一个MySQL查询错误，错误信息为“[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”。根据错误信息，这个错误是由于在ORDER BY子句中的列不在GROUP BY子句中，并且包含了非聚合的列。解决这个错误的方法是修改查询语句，将ORDER BY中的列添加到GROUP BY子句中或者使用聚合函数对ORDER BY列进行聚合。