MySQL中的only_full_group_by模式

一、背景

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘postscan.verifyDelayLog.auditor’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

以上報錯資訊為寫 SQL 時，使用了 GROUP BY 導致報錯，這是因為在 MySQL 5.7 版本中，預設開啟了 only_full_group_by 模式，這也導致很多程式碼在本地跑得好好的，一上線就報錯

對於 GROUP BY 聚合操作，如果在 SELECT 中的列，沒有在 GROUP BY 中出現，那麼這個 SQL 是不合法的，因為列不在 GROUP BY 句中，所以對於設定了這個 mode 的資料庫，在使用 GROUP BY 的時候，就要用 MAX(),SUM(),ANT_VALUE() 這種聚合函數，才能完成 GROUP BY 的聚合操作。

二、解決方法

• 1、檢視資料庫的 sql_mode

select @@global.sql_mode;

查詢結果為：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我們去掉 ONLY_FULL_GROUP_BY，重新設值

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

但是這樣只能解決後面新增的資料庫有效，已經存在的資料庫無效

還需要在已經存在的每個庫中執行：

set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

• 2、修改 MySQL 的組態檔 /etc/my.cnf，新增如下設定

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意：必須新增在 [mysqld] 下面，新增到檔案的最後一行無效

然後重新啟動 MySQL 即可

如您在閱讀中發現不足，歡迎留言！！！