MySQL 預設 only_full_group_by

del88發表於2024-09-29
MySql

ONLY_FULL_GROUP_BY是MySQL中的一個SQL模式，它要求在使用GROUP BY語句時，SELECT列表、HAVING條件或ORDER BY列表中的每個列，要麼是聚合函式的一部分（如COUNT(), SUM(), AVG()等），要麼必須在GROUP BY子句中明確指定。這個模式的設計初衷是增強查詢的準確性和可預測性，避免因為列的不明確引用而導致的資料錯誤或不一致。

以下是ONLY_FULL_GROUP_BY模式的具體舉例：

示例 1：正確使用 ONLY_FULL_GROUP_BY

假設有一個orders表，包含customer_id、order_date和order_amount欄位，我們想要查詢每個客戶的訂單數和訂單總額。在ONLY_FULL_GROUP_BY模式下，正確的查詢應該是這樣的：

SELECT customer_id, COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

在這個查詢中，customer_id列在GROUP BY子句中明確指定，而其他列（order_id和order_amount）則透過聚合函式COUNT()和SUM()進行處理。

示例 2：違反 ONLY_FULL_GROUP_BY 規則的查詢

如果我們嘗試執行以下查詢（在ONLY_FULL_GROUP_BY啟用時）：

SELECT customer_id, order_date, COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

這個查詢會因為order_date列沒有在GROUP BY子句中宣告，且不是聚合函式的一部分，而引發錯誤。MySQL會要求你明確這個欄位的處理方式。

解決方法

  1. 修改查詢：

    • 將order_date列新增到GROUP BY子句中，但這通常不符合查詢的意圖，因為這樣做會按每個客戶的每個訂單日期分組，而不是按客戶分組。
    • 使用聚合函式處理order_date列（如MIN(), MAX()等），但這通常不是想要的結果。
    • 使用ANY_VALUE()函式（如果MySQL版本支援），這個函式可以返回組內的任意一個值，但需要注意這可能會引入不確定性。

  2. 修改SQL模式：

    • 臨時禁用ONLY_FULL_GROUP_BY模式，透過執行如SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));的命令（注意，這種方法在當前會話有效，重啟資料庫後恢復）。
    • 永久禁用ONLY_FULL_GROUP_BY模式，透過修改MySQL的配置檔案（如my.cnf或my.ini），在[mysqld]部分設定sql_mode，去掉ONLY_FULL_GROUP_BY，然後重啟MySQL服務。

示例 3：使用 ANY_VALUE() 函式

如果MySQL版本支援ANY_VALUE()函式，你可以這樣修改查詢：

SELECT customer_id, ANY_VALUE(order_date), COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

這樣，order_date列將不會引發錯誤，但需要注意返回的是組內任意一個訂單的日期。

總的來說，ONLY_FULL_GROUP_BY模式有助於確保SQL查詢的準確性和一致性，但在某些情況下可能需要調整查詢或SQL模式以滿足特定的查詢需求。

相關文章