MySQL分組的時(shí)候遇到ONLY_FULL_GROUP_BY報(bào)錯(cuò)問(wèn)題

一、ONLY_FULL_GROUP_BY 錯(cuò)誤的根本原因

MySQL 5.7 及以上版本默認(rèn)啟用了 sql_mode=only_full_group_by 嚴(yán)格模式。

該模式強(qiáng)制要求：

1. SELECT 中的非聚合字段必須出現(xiàn)在 GROUP BY 子句中；
2. 所有非聚合字段需通過(guò)聚合函數(shù)（如 MAX、MIN、SUM）處理，或顯式聲明分組依據(jù)。

觸發(fā)場(chǎng)景示例：

SELECT name, age, SUM(sales) FROM orders GROUP BY name;  

若 age 未出現(xiàn)在 GROUP BY 中且未使用聚合函數(shù)，MySQL 無(wú)法確定如何為同一 name 的不同 age 值返回結(jié)果，導(dǎo)致報(bào)錯(cuò)。

二、解決方案

1. 調(diào)整 SQL 語(yǔ)句

方案一：添加缺失的字段到 GROUP BY

將所有 SELECT 中的非聚合字段加入分組條件：

SELECT name, age, SUM(sales) FROM orders GROUP BY name, age;  

適用場(chǎng)景：需精確按多字段分組，但可能導(dǎo)致分組維度增加，影響性能。

方案二：使用聚合函數(shù)包裹非分組字段

通過(guò) MAX()、MIN() 或 ANY_VALUE() 處理字段：

SELECT name, MAX(age) AS latest_age, SUM(sales) FROM orders GROUP BY name;  

ANY_VALUE(age) 會(huì)從分組中隨機(jī)選擇一個(gè)值，適用于無(wú)需精確值的場(chǎng)景。

方案三：使用子查詢或臨時(shí)表

將復(fù)雜邏輯拆分為子查詢，分步處理：

WITH grouped_data AS (  
  SELECT name, SUM(sales) AS total_sales  
  FROM orders  
  GROUP BY name  
)  
SELECT g.name, o.age, g.total_sales  
FROM grouped_data g  
JOIN orders o ON g.name = o.name;  

2. 臨時(shí)或永久關(guān)閉 ONLY_FULL_GROUP_BY

臨時(shí)禁用（會(huì)話級(jí)）：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE...';  -- 移除 ONLY_FULL_GROUP_BY  

永久禁用（需修改配置文件）：

[mysqld]  
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE...  -- 去掉 ONLY_FULL_GROUP_BY  

風(fēng)險(xiǎn)：可能導(dǎo)致查詢結(jié)果不可預(yù)測(cè)，僅建議在測(cè)試環(huán)境使用。

3. 使用 ANY_VALUE() 函數(shù)（推薦）

從 MySQL 5.7.5 開(kāi)始，可用 ANY_VALUE() 顯式抑制錯(cuò)誤：

SELECT name, ANY_VALUE(age), SUM(sales) FROM orders GROUP BY name;  

此函數(shù)會(huì)從分組中返回任意一個(gè)值，適用于無(wú)需精確值的業(yè)務(wù)場(chǎng)景。

三、規(guī)避問(wèn)題的建議

遵循嚴(yán)格模式：

• 啟用 ONLY_FULL_GROUP_BY 可提升數(shù)據(jù)準(zhǔn)確性，避免不可預(yù)測(cè)的查詢結(jié)果。
• 強(qiáng)制要求開(kāi)發(fā)人員按規(guī)范編寫(xiě) SQL，確保所有非聚合字段明確處理。

優(yōu)化查詢?cè)O(shè)計(jì)：

• 避免在 SELECT 中引入不必要的字段，減少歧義。
• 優(yōu)先使用聚合函數(shù)或子查詢處理復(fù)雜邏輯。

索引優(yōu)化：

• 為 GROUP BY 涉及的字段和關(guān)聯(lián)條件添加索引，提升性能。

代碼審查與測(cè)試：

• 在代碼審查中檢查 GROUP BY 語(yǔ)句的規(guī)范性。
• 在測(cè)試環(huán)境啟用嚴(yán)格模式，提前暴露問(wèn)題。

總結(jié)

根本矛盾：ONLY_FULL_GROUP_BY 模式通過(guò)嚴(yán)格性保障數(shù)據(jù)一致性，但需要開(kāi)發(fā)者遵循 SQL 標(biāo)準(zhǔn)。

最佳實(shí)踐：

• 優(yōu)先通過(guò)調(diào)整 SQL 語(yǔ)句（如聚合函數(shù)、子查詢）解決問(wèn)題；
• 僅在必要時(shí)臨時(shí)禁用嚴(yán)格模式，生產(chǎn)環(huán)境慎用；
• 利用 ANY_VALUE() 作為靈活性補(bǔ)充，但需評(píng)估業(yè)務(wù)場(chǎng)景的準(zhǔn)確性需求。

通過(guò)上述方法，可在兼容性、性能和數(shù)據(jù)準(zhǔn)確性之間取得平衡。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論