Mac MySQL5.7 預設開啟 ONLY_FULL_GROUP_BY 模式問題與解決方案

邢闖洋發表於2021-12-11
MacMySql模式

背景

MySQL5.7 後將 sql_mode 的 ONLY_FULL_GROUP_BY 模式預設設定為開啟狀態，這樣一來，很多之前的 sql 語句可能會出現錯誤，錯誤資訊如下：

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column '×××' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

描述：select 的列都要在 group 中。

解決方案一

這時我們可以透過 Navicat 或進入 MySQL 命令列終端輸入 select @@global.sql_mode 來檢視當前資料庫的 sql_mode 屬性值

Mac MySQL5.7 預設開啟 ONLY_FULL_GROUP_BY 模式問題與解決方案

複製查詢結果，去掉 ONLY_FULL_GROUP_BY，重新賦值，執行成功後，就可以將ONLY_FULL_GROUP_BY 模式預設設定為關閉狀態，解決之前的 sql 報錯問題

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'

但是這種方式設定的只是當前會話中的sql_model，伺服器重啟後，設定可能會失效。

解決方案二

透過在 my.cnf 配置檔案中寫入 sql_mode 實現關閉 ONLY_FULL_GROUP_BY 永久生效

首先要找到 my.cnf

但是官網說：從 5.7.18 開始不在二進位制包中提供 my-default.cnf 檔案。參考: MySQL 官網

這個 my.cnf 檔案可以是自定義位置，也可以使用如下預設的位置，只要放在預設位置，MySQL 自動識別（透過 deb 或者 APT 源安裝的，初始位置在下方列表）：

FileName Purpose
/etc/my.cnf 全域性選項
/etc/mysql/my.cnf 全域性選項
SYSCONFDIR/my.cnf 全域性選項
$MYSQL_HOME/my.cnf 伺服器特定選項（僅限伺服器）
defaults-extra-file 指定的檔案 –defaults-extra-file，如果有的話
~/.my.cnf 使用者特定選項
~/.mylogin.cnf 使用者特定的登入路徑選項（僅限客戶端）

以上的詳細說明可以參考官網解釋：MySQL 官網

我選擇建立在 /etc/mysql/my.cnf 位置

配置檔案中我增加了一個 mysqld-5.7 組，並配置 sql_mode

配置檔案語法也可參考 MySQL 官網

配置檔案內容如下：

[mysqld-5.7] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

最後重啟 MySQL 伺服器，在次執行 select @@global.sql_mode 就會發現 ONLY_FULL_GROUP_BY 沒有了。

結尾

參考連結：
(34條訊息) MySQL的my.cnf檔案（解決5.7.18下沒有my-default.cnf）_djCode-CSDN部落格

(34條訊息) MySQL5.7預設開啟ONLY_FULL_GROUP_BY模式問題與解決方案_Peacock__的部落格-CSDN部落格_mysql5.7 only_full_group_by

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章