MySQL 之 only_full_group_by

邢闖洋發表於2022-08-21
MySql

開發環境連線的 mysql5.6，而測試環境是 mysql5.7。開發中有小夥伴寫了有關 group by 的 sql 語句。在開發環境中執行是正常的，而到了測試環境中就發現了異常。

原因分析：MySQL5.7 版本預設設定了 mysql sql_mode = only_full_group_by 屬性，導致報錯。

其中 ONLY_FULL_GROUP_BY 就是造成這個錯誤的罪魁禍首了，在這種嚴格模式下，對於 group by 聚合操作，若在 select 中的列沒有在group by 中出現，那麼這個 SQL 就是不合法的。因為開發寫的 sql 中，select 列不在 group by 從句中，在使用 group by 時就會報錯。

測試環境下載安裝的是最新版的 mysql5.7.x 版本，預設是開啟了 only_full_group_by 模式的。

1. 檢視 sql_mode

SELECT` `@@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

2. 去掉ONLY_FULL_GROUP_BY，重新設定值。

SET` `@@``global``.sql_mode =``'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'``;

3. 上面是改變了全域性sql_mode，對於新建的資料庫有效。對於已存在的資料庫，則需要在對應的資料下執行

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'``;

以上方法mysql資料庫重啟後依然無效，下列方式重啟後依然生效

找到 MySQL 的配置檔案,在 linux 系統上 /etc/my.cnf 檔案,查詢 sql_mode 欄位,我並沒有在配置檔案中找到這個關鍵字,所以我手動新增進去:

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 才會生效,退出資料庫:exit,重啟命令:

service mysqld restart

#lnmp重啟mysql
lnmp restart mysql

重新整理頁面報錯資訊消失成功解決,再次連線上資料庫檢視 sql_mode 配置 select @@sql_mode :

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

到此這篇關於 Mysql5.7 及以上版本 ONLY_FULL_GROUP_BY 報錯的解決方法的文章就介紹到這了,更多相關 Mysql5.7 ONLY_FULL_GROUP_BY

以下為sql_mode常用值的含義，參考：
ONLY_FULL_GROUP_BY：對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中
NO_AUTO_VALUE_ON_ZERO：該值影響自增長列的插入。預設設定下，插入0或NULL代表生成下一個自增長值。如果使用者希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。
STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制
NO_ZERO_IN_DATE：在嚴格模式下，不允許日期和月份為零
NO_ZERO_DATE：設定該值，mysql資料庫不允許插入零日期，插入零日期會丟擲錯誤而不是警告。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果資料被零除，則產生錯誤而非警告。如果未給出該模式，那麼資料被零除時MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT建立密碼為空的使用者
NO_ENGINE_SUBSTITUTION：如果需要的儲存引擎被禁用或未編譯，那麼丟擲錯誤。不設定此值時，用預設的儲存引擎替代，並丟擲一個異常
PIPES_AS_CONCAT：將”||”視為字串的連線運算子而非或運算子，這和Oracle資料庫是一樣的，也和字串的拼接函式Concat相類似
ANSI_QUOTES：啟用ANSI_QUOTES後，不能用雙引號來引用字串，因為它被解釋為識別符

參考文章

MySQL之only_full_group

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

相關文章