MySQL GROUP BY兼容性陷阱：SELECT COUNT(*) 與非聚合欄位的版本差異

SQL語句SELECT COUNT(id), id, name FROM table在MySQL 5.5執行通過（返回第一行id/name），但在5.7+嚴格模式（ONLY_FULL_GROUP_BY）報錯：#1140 - Mixing of GROUP columns ... with no GROUP BY clause is illegal。本文剖析版本差異根因與規範寫法。

錯誤重現

SELECT COUNT(id), id, name FROM `table`;
-- 5.5/5.6 (非嚴格模式)：執行成功，id/name取結果集第一行（非確定性）
-- 5.7+ (預設 ONLY_FULL_GROUP_BY)：報錯 1140

根因分析

• SQL標準：SELECT列表中非聚合欄位必須出現在GROUP BY中，或被聚合函數包裹
• MySQL歷史妥協：早期為方便允許「隱式分組」，僅取第一行值，導致語義模糊、結果不確定
• 5.7預設開啟：sql_mode含ONLY_FULL_GROUP_BY，強制標準化

規範寫法對比

版本兼容策略

1. 代碼層面：嚴格遵循SQL標準，所有非聚合欄位入GROUP BY
2. 會話級關閉（不推薦長期）：SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')
3. 全局關閉（極不推薦）：my.cnf中移除ONLY_FULL_GROUP_BY
4. 升級驗證：WAMP/XAMPP等集成環境切換版本測試，CI/CD管道跑測試套件

窗口函數現代解法（MySQL 8.0+）

SELECT 
  id, name,
  COUNT(*) OVER() AS total_cnt,
  ROW_NUMBER() OVER(ORDER BY id) AS rn
FROM `table`
WHERE rn = 1;  -- 取第一行並附帶總數

遵循標準SQL分組語義，拒絕「偏門寫法」，讓代碼在版本升級中平滑遷移。