1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

时间: 2023-10-12 14:06:04 浏览: 101

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数据库中常见的错误之一，错误代码为1055。该错误通常出现在使用GROUP BY子句时，ORDER BY子句中引用了未包含在GROUP BY子句中的列。这是因为在MySQL的新版本中，默认启用了"only_full_group_by"模式，该模式要求在使用GROUP BY子句时，SELECT语句中的列必须要么在GROUP BY子句中出现，要么作为聚合函数的参数。要解决这个错误，有以下几种方法： 1. 将ORDER BY子句中的列添加到GROUP BY子句中，以使其包含所有被引用的列。这样可以确保查询结果的正确性，但可能会导致结果不如预期。 2. 在ORDER BY子句中使用聚合函数而不是列名。这样可以绕过only_full_group_by模式的限制，但可能会改变查询结果的排序方式。 3. 修改MySQL的配置文件，将sql_mode参数中的"only_full_group_by"模式移除或修改为其他适合的模式。这样可以全局禁用或修改only_full_group_by模式，但可能会影响其他查询的执行。需要注意的是，选择哪种方法取决于具体的业务需求和查询的目的。在修改查询语句或MySQL配置之前，建议先仔细分析查询的逻辑和结果，并确保所采取的方法符合预期。123 #### 引用[.reference_title] - *1* [MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona...](https://download.csdn.net/download/weixin_38742656/13685329)[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^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [解决MySQL报错：1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘in...](https://blog.csdn.net/HaHa_Sir/article/details/80503601)[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^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [mysql5.7 group报错ERROR 1055 (42000): Expression #1](https://blog.csdn.net/qq_38157006/article/details/89952307)[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^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]

阅读全文

1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

相关推荐

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

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

SQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

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

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

[err] 1055 - expression #1 of order by clause is not in group by clause and contains nonaggregated c

[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

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

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

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

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

SQL 错误 [1055] [42000]: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'rlstudy.s.SCORE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

【创新未发表】鸽群算法PIO-Kmean-Transformer-LSTM负荷预测Matlab源码 9523期.zip

13丨为什么我们需要Pod？W.jpg

官方 TinyMCE Vue 组件.zip

Vue3 + Vite5 + TypeScript + Element-Plus 构建的后台管理前端模板，配套接口文档和后端源码，vue-element-admin 的 Vue3 版本

精细金属掩模板(FMM)行业研究报告 显示技术核心部件FMM材料产业分析与市场应用

yolo算法-猴痘图像数据集数据集-217张图像带标签-猴痘.zip

中国面膜微商行业市场研究报告 市场研究：微商行业·面膜市场·用户行为分析·发展趋势

最新推荐

【创新未发表】鸽群算法PIO-Kmean-Transformer-LSTM负荷预测Matlab源码 9523期.zip

13丨为什么我们需要Pod？W.jpg

官方 TinyMCE Vue 组件.zip

Vue3 + Vite5 + TypeScript + Element-Plus 构建的后台管理前端模板，配套接口文档和后端源码，vue-element-admin 的 Vue3 版本

精细金属掩模板(FMM)行业研究报告 显示技术核心部件FMM材料产业分析与市场应用

Angular实现MarcHayek简历展示应用教程

管理建模和仿真的文件

深入剖析：内存溢出背后的原因、预防及应急策略（专家版）

Java中如何对年月日时分秒的日期字符串作如下处理：如何日期分钟介于两个相连的半点之间，就将分钟数调整为前半点

Crossbow Spot最新更新 - 获取Chrome扩展新闻

精细金属掩模板(FMM)行业研究报告显示技术核心部件FMM材料产业分析与市场应用

中国面膜微商行业市场研究报告市场研究：微商行业·面膜市场·用户行为分析·发展趋势

精细金属掩模板(FMM)行业研究报告显示技术核心部件FMM材料产业分析与市场应用