group by的語法在mysql升級後後就不正常了，怎麼回事？ @新精讚

[無邊框]

2219

group by的語法在mysql升級後後就不正常了，怎麼回事？

“

ONLY_FULL_GROUP_BY這個變數是怎樣，讓我原本可以用的語法失效了？

„

分享此文連結 //n.sfs.tw/13050

分享連結 group by的語法在mysql升級後後就不正常了，怎麼回事？@新精讚

(文章歡迎轉載，務必尊重版權註明連結來源)

2019-10-25 01:00:17 最後編修

2019-01-21 17:01:00 By 張○○

自動目錄

系統及版本
解決的方法

mysql 到了5.6.5版之後，把一個稱為 ONLY_FULL_GROUP_BY 參數啟動後，原來很多語法就報錯了。

以下的範例來自於mysql官網[1]。

情況1

要選取的address欄位沒有在group by 的項目中。

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;

ERROR 1055 (42000): 't.address' isn't in GROUP BY

情況2

having語法中使用的 max_age欄位不存在group by 項目中

mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name HAVING max_age < 30;

ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause

情況3

MAX函數要和group by 一起用

SELECT name, MAX(age) FROM t;

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

這樣很不方便，因為有時其它的欄位是拿來參考用的，至少群組時會列舉第一筆，這樣比對資料很方便。

系統及版本

select @@version

10.1.28-MariaDB

Freebsd 11

解決的方法

查看現有參數

SELECT @@sql_mode

mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

把那個 ONLY_FULL_GROUP_BY 移掉，使用replace函數[3]。

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

如果要加回去

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

若有其他sql_mode也用相同的方法處理

重新啟動mysql