Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

时间: 2023-11-17 19:59:34 浏览: 270

MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…

5星 · 资源好评率100%

[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 dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 在navicat中使用mysql加索引或者删除索引时报了这个错。两种解决方在MySQL中，错误提示 "[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column…” 指的是在执行包含`GROUP BY`子句的查询时，你试图对未在`GROUP BY`子句中出现的列进行排序，且该列没有被聚合函数（如`SUM`, `AVG`, `COUNT`, `MAX`, `MIN`等）处理。这种行为与`sql_mode=only_full_group_by`设置不兼容，因为该模式要求所有出现在`ORDER BY`中的列必须在`GROUP BY`中或者被聚合函数所引用。 `sql_mode=only_full_group_by`是MySQL的一种严格模式，它确保了查询结果的可预测性和一致性，避免了因隐式聚合可能引发的不一致数据。在非严格模式下，MySQL可能会选择任意行的数据来代表每个组，而在严格模式下，这样做会被视为错误。当你在Navicat中尝试为MySQL表添加或删除索引时遇到这个错误，你可以采取以下两种解决方案： 1. **临时解决方法**： - 你可以检查当前的`SQL_MODE`设置： ```sql SELECT @@sql_mode; ``` - 接着，修改`SQL_MODE`以移除`ONLY_FULL_GROUP_BY`： ```sql SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ``` 这样会临时关闭`only_full_group_by`模式，让你能够执行查询。但请注意，这只会对当前会话有效，重启数据库服务后会恢复原状。 2. **永久性解决方法**： - 找到MySQL配置文件`my.ini`，通常位于MySQL的安装目录下。如果你不确定路径，可以在Navicat中执行如下语句获取： ```sql SELECT @@datadir; ``` - 编辑`my.ini`文件，在`[mysqld]`段下，找到`sql_mode`设置，并将`ONLY_FULL_GROUP_BY`移除，例如： ``` [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服务，这样就永久改变了`sql_mode`设置。以上两种方法可以帮助你解决在Navicat中遇到的这个错误。然而，建议在生产环境中谨慎使用这种方法，因为`only_full_group_by`模式有助于防止潜在的错误和数据不一致。如果你的查询确实需要这样的排序，你应该考虑调整查询语句，将`ORDER BY`中的列包含在`GROUP BY`子句中，或者使用聚合函数来处理这些列。这不仅可以使你的代码更符合规范，也有助于提高查询的可读性和维护性。

这个错误信息是MySQL数据库返回的错误信息，意思是在SELECT语句中，第三个选择的列不在GROUP BY子句中，且不是聚合函数，这与当前的SQL模式(only_full_group_by)不兼容。这个错误通常是由于在GROUP BY子句中没有包含所有的非聚合列而导致的。解决这个问题的方法是要么将所有的非聚合列都包含在GROUP BY子句中，要么将它们转换为聚合函数。

阅读全文

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关推荐

解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

mysql遇到Expression.docx

Mysql：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

ause: java.sql.SQLSyntaxErrorException: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column

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

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

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

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

Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bookstore.book.图书编号' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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

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

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

1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sma怎么解决

Expression #3 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. macbook怎么解决这个报错

pymysql.err.OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

最新推荐

A级景区数据文件json

使用Java编写的坦克大战小游戏.zip学习资料

【python毕设】p073基于Spark的温布尔登特色赛赛事数据分析预测及算法实现_flask(5).zip

JHU荣誉单变量微积分课程教案介绍

管理建模和仿真的文件

【实战篇：自定义损失函数】：构建独特损失函数解决特定问题，优化模型性能

如何在ZYNQMP平台上配置TUSB1210 USB接口芯片以实现Host模式，并确保与Linux内核的兼容性？

Naruto爱好者必备CLI测试应用

"互动学习：行动中的多样性与论文攻读经历"

【强化学习损失函数探索】：奖励函数与损失函数的深入联系及优化策略