[精讚] [會員登入]
2471

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] 表格的引擎 (engine):查看及修改 MySQL表格引擎預設為 MyISAM,但在4.0以上的mysql 其實支援不只一種引擎,各有優缺點,這篇介紹引擎的操作。

[Mysql] 建立叢集式資料庫1/4 -- 安裝及設定DB1 使用galera建立Mysql的叢集式資料庫 (GALERA CLUSTER FOR MYSQL THE TRUE MULTI-MASTER)

[Mysql/Mariadb] 密碼設定強度修改 新的mysql對於密碼預設也把原來的規則改得更嚴格,此篇教你修改密碼的設定政策

兩次使用InnoDB的慘痛經驗 Mysql 的Innodb引擎雖然好用,但是我得說說我兩次的慘痛經驗,這讓我考慮以後可能不會再使用innodb了

[Mysql/Mariadb] 利用phpMyAdmin 建立關聯式資料表 利用phpMyAdmin 建立關聯式資料表

[Mysql] Mysqldump 的預設編碼 --default-character-set 有時Mysqldump 也得要指定編碼才不會造成匯出的亂碼

隨機好文

[HP DL380G7] 生效啟動第3,4片網卡/開啟或關閉內建的網卡 HP DL380G7 預設第3,4片網卡裝完系統後找不到,難道是壞了?要怎麼辦?

為什麼要重造輪子? 什麼輪子?造什麼輪子?我為什麼要重造輪子?

[CodeIgniter3] 解決無法上傳特定檔案(.sb2)的問題 上傳時出現The filetype you are attempting to upload is not allowed,要怎麼解決?

NETCRAFT發現你的網站及作業系統 NETCRAFT可以發現你的網站及作業系統

[PHP] 檢查IP是否在某個網段內 mtachcidr 要檢查IP是否在某個網段內,要寫幾行?10行?5行? 不用,只要2行。以下是我寫的 code /** * matchCI