[精讚] [會員登入]
2375

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

 

參考資料

[1] https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html

[2] https://blog.csdn.net/lm409/article/details/73065217

[3] https://stackoverflow.com/questions/23921117/disable-only-full-group-by

END

你可能感興趣的文章

[Mysql/Mariadb] Left Join 和 Right Join 合併表單 SQL語法中的 Left Join 和 Right Join 很常用,但是我常會忘記,所以寫下來備忘。

[Mysql] 修改欄位的自動索引值autoindex (auto_increment ) Mysql 新增、刪除、修改、查看欄位的自動索引值autoindex (auto_increment)

[Mysql/Mariadb] 忘記root密碼 常常會忘記root 密碼,簡單的幾步驟回復

[Mysql] 使用union合併兩個表 mysql合併兩個表的方法,使用union

Mysql INSERT ... ON DUPLICATE KEY UPDATE 寫法,MYSQL如果存在的修改,不存在就新增的寫法 Mysql有則修改,無則新增的sql寫法

[Mysql/Mariadb] 檢查、修復及優化資料庫 mysql/mariadb 檢查、修復及優化資料庫

我有話要說

>>

限制:留言最高字數1000字。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

UTF-8 BOM (Byte Order Mark) 的問題 在 Michael Kaplan 那看到 Every character has a story #4: U+feff

Linux shell 的date表示法 linux下SHELL中的date表示法

世紀帝國征服者新版本--被遺忘的帝國 世紀帝國征服者新版本--被遺忘的帝國 世紀二代的征服者是精典遊戲中的精典,aofe更好玩...

魔球中小女孩唱的歌 The show 魔球中小女孩唱的歌 The show

「許功蓋」的字以及源由 有玩過電腦一段時間的人,都聽過這個人(有一天我才發現7年級的竟然都不認識這個