如何修复 MySQL 错误代码 1055

已发表: 2024-03-28

MySQL 错误代码 1055 与启用 SQL 模式“ONLY_FULL_GROUP_BY”有关,这会影响 GROUP BY 子句的解释和执行方式。 让我们一起解开这个错误,探索它的变化、根本原因,以及最重要的是,如何修复它。

了解错误

MySQL错误代码1055是在涉及SELECT语句和GROUP BY子句的特定情况下触发的。 此要求旨在防止将数据分组在一起的查询产生不明确的结果。

实际上,当您在查询中对数据进行分组时,MySQL 需要知道如何处理属于同一组中未包含在 GROUP BY 子句中的列的多行。 ONLY_FULL_GROUP_BY 模式是 MySQL 通过强制执行这些规则来确保任何分组查询清晰且无歧义的方式。

因此,当您遇到错误 1055 时,MySQL 本质上是在要求澄清:“对于您选择的列,要么告诉我如何对它们进行分组(通过将它们包含在 GROUP BY 子句中),要么告诉我如何聚合数据(通过使用 COUNT()、SUM() 等函数)。”

了解ONLY_FULL_GROUP_BY的作用

这种模式对于保证数据检索操作的准确性和可靠性起着至关重要的作用,尤其是在复杂的数据库环境中。

当启用 ONLY_FULL_GROUP_BY 时,MySQL 会强制执行一条明确的规则:如果某个列出现在包含 GROUP BY 子句的查询的 SELECT 列表中,则该列必须满足以下条件之一:

成为聚合函数:列在聚合函数(如 COUNT()、SUM()、MAX() 等)中使用。这些函数汇总来自分组在一起的多行的数据,每组生成一个结果。

在功能上依赖于 GROUP BY 列:该列在功能上依赖于 GROUP BY 子句中列出的列。函数依赖意味着一列(函数依赖列)的值由另一列或多列(GROUP BY 子句中的列)唯一确定。 简单来说,对于 GROUP BY 列的任何给定值,功能相关列恰好有一个可能的值。

错误的变化

以下是此错误可能出现的一些方式:

  • MySQL 错误代码 1055:SELECT 列表的表达式 #1 不在 GROUP BY 子句中,并且包含非聚合列“database.table.column”,该列在功能上不依赖于 GROUP BY 子句中的列; 这与 sql_mode=only_full_group_by 不兼容
  • 错误 1055:ONLY_FULL_GROUP_BY:某些内容不在 GROUP BY 子句中
  • SQL 错误 (1055):SELECT 列表的表达式不在 GROUP BY 子句中
  • SQLSTATE[42000]:语法错误或访问冲突:1055

出现此错误的原因

MySQL 错误代码 1055 的发生主要是由于启用了 SQL 模式“ONLY_FULL_GROUP_BY”,该模式对使用 GROUP BY 子句的查询要求更严格的规则。 主要原因如下:

SQL 模式“ONLY_FULL_GROUP_BY”已启用:此模式要求所有 SELECT 列表表达式出现在 GROUP BY 子句中,除非它们被聚合。它旨在防止出现不明确的查询结果。

SELECT 列表中的非聚合列:如果您的 SELECT 列表包含未包含在聚合函数中的列(例如 SUM、AVG、MIN、MAX、COUNT),并且这些列不是 GROUP BY 子句的一部分,MySQL 会将其标记为错误`ONLY_FULL_GROUP_BY`。

功能依赖:MySQL 期望 SELECT 列表中未聚合的列在功能上依赖于 GROUP BY 子句。功能依赖意味着一列唯一地确定另一列。 如果不满足此关系,MySQL 将抛出错误 1055。

修复 MySQL 错误代码 1055

现在我们已经了解了错误代码 1055 的含义以及导致该错误的原因,接下来让我们看一下修复此错误的几种方法。

分析查询

解决 MySQL 错误代码 1055 的初始步骤包括分析触发此错误的查询。 此检查的目的是查明 SELECT 子句中列出的列,这些列既不包含在 GROUP BY 子句中,也不聚合。 这个差异是问题的核心,因为 MySQL 在 ONLY_FULL_GROUP_BY SQL 模式下,要求 SELECT 列表中引用的任何列(而不是聚合函数)必须在 GROUP BY 子句中声明,以保证结果明确。

调整您的查询

要纠正此错误,需要对查询进行调整。 修改重点是与 ONLY_FULL_GROUP_BY 规定保持一致。 以下是实现合规性的两个主要策略:

包括 GROUP BY 中的所有非聚合列

全面包含:最直接的方法是确保 SELECT 列表中出现的每一列(不属于聚合函数(如 COUNT、AVG、SUM 等))都包含在 GROUP BY 子句中。 此方法符合 GROUP BY 操作的 SQL 标准,保证结果集中的每一行都由 GROUP BY 子句中指定的列唯一标识。

例子

假设您有一个名为“sales”的数据库表,其中包含“date”、“region”和“amount”列。 您想要查找每个区域的总销售额,但您的初始查询触发了错误 1055。

初始查询(导致错误 1055)

 选择日期、地区、SUM(金额)

来自销售

按地区分组;

此查询会导致错误,因为“date”未包含在 GROUP BY 子句中并且未在聚合函数中使用。

修改查询(解决错误 1055)

 选择日期、地区、SUM(金额)

来自销售

按日期、地区分组;

通过在 GROUP BY 子句中同时包含“date”和“region”,查询符合“ONLY_FULL_GROUP_BY” SQL 模式,从而解决了错误。

对非分组列使用聚合函数

策略聚合:在 GROUP BY 子句中包含所有非聚合列不切实际或偏离查询意图的情况下,对非分组列使用聚合函数是一种有效的替代方案。此方法将非唯一数据合并为有意义的聚合结果,从而规避 ONLY_FULL_GROUP_BY 限制。

例子

让我们使用相同的“销售”表。 假设您要选择区域和最晚销售日期,但仅按“区域”分组。 将聚合函数应用于“日期”列可以实现此目的,而无需将其包含在 GROUP BY 子句中。

初始查询(导致错误 1055)

 选择地区、日期

来自销售

按地区分组;

此查询将失败,因为“date”既不在聚合函数中,也不在 GROUP BY 子句中。

修改查询(解决错误 1055)

 选择区域,MAX(日期) ASlatest_sale_date

来自销售

按地区分组;

在此修改后的查询中,“MAX()”函数用于聚合“date”列,从而选择每个区域的最新销售日期。 这通过遵守“ONLY_FULL_GROUP_BY”要求解决了错误,因为“region”是 GROUP BY 子句中的唯一列,并且“date”在聚合函数中使用。

禁用“ONLY_FULL_GROUP_BY” SQL 模式

当查询逻辑要求包含无法添加到 GROUP BY 子句或聚合的列时,禁用 MySQL 中的“ONLY_FULL_GROUP_BY”模式可以被视为最后的手段。

禁用此模式可能会解决 MySQL 错误代码 1055 等直接问题,但它会带来生成不确定查询结果的风险。 这种不确定性可能会导致查询在不同条件下返回不同的结果,从而可能导致生产环境中的数据不一致和难以调试的问题。

如何禁用“ONLY_FULL_GROUP_BY”

要暂时禁用当前数据库会话的“ONLY_FULL_GROUP_BY”(这意味着更改仅在连接期间持续),请使用以下 SQL 命令:

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

此命令有选择地从会话的 SQL 模式中删除“ONLY_FULL_GROUP_BY”,而不影响全局 SQL 模式配置或影响其他数据库会话。

全球范围

对于影响与 MySQL 服务器的所有新连接的更永久的解决方案,您可以使用以下命令更改全局 SQL 模式设置:

 设置全局 sql_mode=(选择替换(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

执行此命令会通过删除“ONLY_FULL_GROUP_BY”来更改服务器范围的 SQL 模式设置,从而将更改应用于执行后的所有新连接。 值得注意的是,这不会影响现有连接,并且需要重新启动服务器或新连接才能全局应用更改。

管理 MySQL 查询的最佳实践

在处理 MySQL 查询时,尤其是在涉及复杂分组和聚合函数的情况下,采用一组最佳实践至关重要。 这些做法不仅可以确保查询的效率和正确性,还有助于维护数据的完整性和可靠性,特别是当您绕过“ONLY_FULL_GROUP_BY” SQL 模式等约束时。

了解数据关系:深入了解数据库中列之间的关系和依赖关系。了解数据如何互连有助于构建更有效的查询并避免错误或意外结果。

熟悉架构:定期熟悉数据库架构。这种熟悉有助于识别要按哪些列进行分组以及哪些列可以在聚合函数中有效使用。

策略应用:精心应用聚合函数(例如`SUM()`、`AVG()`、`COUNT()`等)以实现特定的分析目标。这些函数是总结和分析数据的强大工具,但如果使用不当,可能会导致误导性结果。

避免过度聚合:在没有适当分组的情况下,不要在大型​​数据集上过度使用聚合函数,因为这可能会导致性能问题,并可能掩盖您试图从数据中收集的见解。

确保预期结果:对查询进行调整后(例如禁用“ONLY_FULL_GROUP_BY”或更改查询结构),必须广泛测试这些更改。此测试可确保查询返回预期结果并保持数据完整性。

性能评估:测试不仅仅关乎正确性;还关乎正确性。这也与性能有关。 评估查询对数据库性能的影响,尤其是在处理大型数据集或复杂聚合时。

结束语

我们已经解决了如何修复 MySQL 错误代码 1055,重点是调整查询以符合“ONLY_FULL_GROUP_BY”,或者作为最后的手段,禁用此模式。 我们重点介绍了确保数据完整性和查询准确性的最佳实践,强调理解和遵守 SQL 标准的重要性,以防止出现问题并确保可靠的数据库操作。

利用 AI 加速您的 WordPress 网站创建 使用

10Web AI Website Builder,创建适合您业务需求的自定义 WordPress 网站,速度提高 10 倍。

生成您的网站
无需信用卡