[精讚] [會員登入]
2401

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] 使用Memory引擎修改及設定最大值的方法 Mysql/MariaDB 使用Memory引擎預設是16MB,修改及設定最大值的方法。

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

[Mysql] 使用索引來加速搜尋 只要是常常用到搜索條件的欄位,就應該把它設成索引鍵,有無設定的速度差可能會好幾倍

[Mysql/Mariadb] 全文檢索 fulltext index 關於 MySQL 的全文檢索

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

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

隨機好文

PHP for sphinx 函式庫安裝 PECL/sphinx PHP>= 5.2.2 已經能原生支援 sphinx,可是預設的沒有裝,我們得自己裝才能用

TFTP Server 安裝及使用 讓設備的網路設定檔或是韌體經由TFTP拷備出來,操作的方法

此一時彼一時 我是不是易怒的人,其實我也不知道,總之我常會失控,不知道自己在幹嘛。這近生活過得浮浮的,不是很踏實,總會想太多,我甚至會

對物品的感情 這個議題很奇怪,可是大部分的人都會有,物品是死的,壞了就淘汰,出新的就被替換。為何會有情感?

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