MySQL設置only_full_group_by報錯如何解決

這篇文章主要講解了“MySQL設置only_full_group_by報錯如何解決”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL設置only_full_group_by報錯如何解決”吧！

開發(fā)環(huán)境連接的 mysql5.6，而測試環(huán)境是 mysql5.7。開發(fā)中有小伙伴寫了有關 group by 的 sql 語句。在開發(fā)環(huán)境中運行是正常的，而到了測試環(huán)境中就發(fā)現(xiàn)了異常。

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

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

測試環(huán)境下載安裝的是最新版的 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，對于新建的數(shù)據(jù)庫有效。

對于已存在的數(shù)據(jù)庫，則需要在對應的數(shù)據(jù)下執(zhí)行

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數(shù)據(jù)庫重啟后依然無效，下列方式重啟后依然生效

找到 MySQL 的配置文件,在 linux 系統(tǒng)上 /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] 配置內(nèi),這樣添加完后重啟mysql 才會生效,退出數(shù)據(jù)庫:exit,重啟命令:

service mysqld restart

#lnmp重啟mysql
lnmp restart mysql

刷新頁面報錯信息消失成功解決,再次連接上數(shù)據(jù)庫查看 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

以下為sql_mode常用值的含義，參考：

ONLY_FULL_GROUP_BY：對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個SQL是不合法的，因為列不在GROUP BY從句中

NO_AUTO_VALUE_ON_ZERO：該值影響自增長列的插入。默認設置下，插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0，而該列又是自增長的，那么這個選項就有用了。

STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制

NO_ZERO_IN_DATE：在嚴格模式下，不允許日期和月份為零

NO_ZERO_DATE：設置該值，mysql數(shù)據(jù)庫不允許插入零日期，插入零日期會拋出錯誤而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數(shù)據(jù)被零除，則產(chǎn)生錯誤而非警告。如果未給出該模式，那么數(shù)據(jù)被零除時MySQL返回NULL

NO_AUTO_CREATE_USER：禁止GRANT創(chuàng)建密碼為空的用戶

NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設置此值時，用默認的存儲引擎替代，并拋出一個異常

PIPES_AS_CONCAT：將”||”視為字符串的連接操作符而非或運算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似

ANSI_QUOTES：啟用ANSI_QUOTES后，不能用雙引號來引用字符串，因為它被解釋為識別符。

感謝各位的閱讀，以上就是“MySQL設置only_full_group_by報錯如何解決”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對MySQL設置only_full_group_by報錯如何解決這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！