如何修復 MySQL 錯誤碼 1055

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 標準的重要性，以防止問題並確保可靠的資料庫操作。