解決MySQL this is incompatible with sql_mode=only_full_group_by 問題
一、錯誤原因分析
出現(xiàn)這個問題是因?yàn)?,對于GROUP BY操作,如果在SELECT中的列,沒有在GROUP BY中出現(xiàn),那么這個SQL是不合法的,因?yàn)榱胁辉贕ROUP BY從句中。
也就是說如果我分組查詢,比如我表里有三個字段 id、name、type,我根據(jù)type分組查詢,那么結(jié)果集里就不能包含name字段。
# 錯誤查詢 會出現(xiàn)this is incompatible with sql_mode=only_full_group_by SELECT `name`,`type` FROM user_01 GROUP BY `type`; # 正確查詢 不會出現(xiàn)問題 SELECT `type` FROM user_01 GROUP BY `type`;
二、原理分析&解決方案分析
- 要想解決這個問題首先要知道MySQL的 sql_mode 是什么,sql_mode 是個很容易被忽視的變量,sql_mode 是規(guī)范一些數(shù)據(jù)庫校驗(yàn)規(guī)則,比如這里出現(xiàn)的sql_mode=only_full_group_by 問題,only_full_group_by 就是一個校驗(yàn)規(guī)則,會規(guī)定分組查詢結(jié)果集不能有GROUP BY中沒有出現(xiàn)的列。
- 這個錯誤發(fā)生在mysql 5.7.5 版本及以上版本會出現(xiàn)的問題,mysql 5.7.5版本以上默認(rèn)的 sql_mode 是包含 only_full_group_by ,這個配置嚴(yán)格執(zhí)行了"SQL92標(biāo)準(zhǔn)",很多從5.6升級到5.7時,為了語法兼容,大部分都會選擇調(diào)整sql_mode,使其保持跟5.6一致,為了盡量兼容程序。
有兩種解決邏輯:
- 第一種:就是順應(yīng)這個規(guī)則,別在GROUP BY是返回額外的列,當(dāng)然,竟然SQL都這樣寫了肯定是需要額外的列了,我們看第二種方法
- 第二種:放開限制就行,將sql_mode 中的 only_full_group_by 剔除就行。
二、臨時解決(不需要重啟MySQL,適配所有版本和任意安裝方式的MySQL,重啟MySQL后會失效)
臨時解決這個問題可以不用重啟MySQL服務(wù),,通過 set global 命令直接改就行。
1、查看當(dāng)前 sql_mode (我這里MySQL的版本是8.0,其它版本也是一樣的做法)
登錄數(shù)據(jù)庫執(zhí)行下面命令可以看到當(dāng)前包含的 sql_mode ,不同版本MySQL結(jié)果可能不同,但是我們的問題是因?yàn)?only_full_group_by 引起的,我們看看里面是否有這個。
SHOW VARIABLES LIKE "sql_mode";
這里可以看到,我的 sql_mode 中第一個就是 only_full_group_by
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
2、臨時修改 sql_mode
將剛剛查詢到的 sql_mode 刪除里面的 ONLY_FULL_GROUP_BY,重新設(shè)置到系統(tǒng)配置中即可。
# 修改全局配置 sql_mode set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; # 修改session配置 sql_mode set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
PS:如果是使用代碼(JDBC)連接的數(shù)據(jù)庫,修改完成之后最好重啟一下代碼服務(wù),mysql服務(wù)不用重啟,如果不重啟可能還是會出現(xiàn)該問題,重啟后生效。
三、徹底解決(需要重啟MySQL才會生效)
要徹底解決這個問題就是將臨時解決方案中的 sql_mode 通過配置文件設(shè)置即可,建議是和臨時方案一起使用,先通過臨時方案讓業(yè)務(wù)服務(wù)可用,然后在配置文件也配置 sql_mode 下一次MySQL重啟后生效。
1、window 配置 sql_mode
window 上的 MySQL 配置文件默認(rèn)是在 C:\Program Files\MySQL\MySQL Server 8.0\my-default.ini,打開這個 my-default.ini 文件找到[mysql],在 [mysqld] 下設(shè)置 sql_mode ,將通過SHOW VARIABLES LIKE "sql_mode";
查詢出來的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
重啟MySQL:
- 打開Windows的服務(wù)管理器,可以通過快捷鍵Win+R打開運(yùn)行窗口,輸入services.msc后按回車鍵。
- 找到MySQL服務(wù),服務(wù)名稱可能會叫MySQLxx(xx是版本信息),右鍵點(diǎn)擊,選擇停止服務(wù)。
- 等待MySQL服務(wù)停止后,再右鍵點(diǎn)擊MySQL服務(wù),選擇啟動服務(wù)。
- 重啟MySQL服務(wù)后,可以通過以下命令檢查MySQL是否已正常運(yùn)行
重啟好在MySQL客戶端執(zhí)行SHOW VARIABLES LIKE "sql_mode";
查詢 sql_mode 是否已經(jīng)改變。
2、Linux 配置 sql_mode
Linux 上的 MySQL 配置文件默認(rèn)是在 /etc/mysql/my.cnf,打開這個 my.cnf 文件找到[mysql],在 [mysqld] 下設(shè)置 sql_mode ,將通過SHOW VARIABLES LIKE "sql_mode";
查詢出來的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。
# 打開/etc/mysql/my.cnf vi /etc/mysql/my.cnf
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
重啟MySQL:service mysqld restart
或 service mysql restart
3、docker 配置 sql_mode
docker 安裝的 linux版本MySQL 配置文件默認(rèn)是在也是容器的 /etc/mysql/my.cnf 中,不過有點(diǎn)區(qū)別的是我們一般去直接改容器中的配置文件,而是會將額外配置文件路徑掛載出來,而且容器中是沒有 vi 這種工具不方便直接改配置,這里會對有做掛載和沒有做掛載的修改方式都說做說明。
使用docker安裝的MySQL的額外配置文件是在容器的 /etc/mysql/conf.d 目錄的,MySQL會讀取這個目錄下所以以 .cnf 結(jié)尾的文件,如果有對這個目錄做掛載最好,如果沒有做掛載也可用通過宿主機(jī)copy到容器內(nèi)部。
1、配置文件準(zhǔn)備
我的配置文件目錄是掛載到了 /home/docker/mysql/conf.d,自己根據(jù)實(shí)際情況調(diào)整。
# 創(chuàng)建一個配置文件 vi /home/docker/mysql/conf.d/mysql.cnf
將下面的內(nèi)容寫入 mysql.cnf 保存即可
[mysqld] sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
2、如果沒有做掛載需要將這個配置文件拷貝到容器內(nèi)(有做掛載跳過)
docker cp /home/docker/mysql/conf.d/mysql.cnf 容器ID:/etc/mysql/conf.d/mysql.cnf
3、重啟容器
docker restart 容器ID/容器名稱
4、檢驗(yàn)是否修改成功
# 進(jìn)入容器 docker exec -it 容器ID/容器名稱 /bin/bash # 連接MySQL mysql -h127.0.0.1 -uroot -p # 查看sql_mode是否被修改成功 SHOW VARIABLES LIKE "sql_mode";
到此這篇關(guān)于解決MySQL this is incompatible with sql_mode=only_full_group_by 問題的文章就介紹到這了,更多相關(guān)MySQL incompatible with內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- 解決MySQL 5.7.9版本sql_mode=only_full_group_by問題
- MySql版本問題sql_mode=only_full_group_by的完美解決方案
- MySQL錯誤提示:sql_mode=only_full_group_by完美解決方案
- 解決MySql版本問題sql_mode=only_full_group_by
- mysql報錯sql_mode=only_full_group_by解決
- mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式
- mysql 8.0 找不到my.ini配置文件以及報sql_mode=only_full_group_by解決方案
- 如何解決MySQL?this?is?incompatible?with?sql_mode=only_full_group_by問題
- MySQL報錯sql_mode=only_full_group_by的問題解決
相關(guān)文章
mysql group by 對多個字段進(jìn)行分組操作
這篇文章主要介紹了mysql group by 對多個字段進(jìn)行分組操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-09-09MySQL select count(*)計數(shù)很慢優(yōu)化方案
這篇文章主要介紹了MySQL select count(*)計數(shù)很慢優(yōu)化方案,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價值,需要的小伙伴可以參考一下2022-08-08MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因
這篇文章主要介紹了MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因,幫助大家更好的理解和學(xué)習(xí)使用MySQL,感興趣的朋友可以了解下2021-05-05mysql 連接出現(xiàn)Public Key Retrieval is n
在MySQL連接中出現(xiàn)“Public Key Retrieval is not allowed”錯誤,通常是因?yàn)樵谑褂冒踩捉幼謱樱⊿SL)連接時遇到了問題,本文就來介紹一下解決方法,感興趣的可以了解一下2024-03-03