一、錯誤原因分析

出現(xiàn)這個問題是因為，對于GROUP BY操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個SQL是不合法的，因為列不在GROUP BY從句中。
也就是說如果我分組查詢，比如我表里有三個字段 id、name、type，我根據(jù)type分組查詢，那么結果集里就不能包含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ù)庫校驗規(guī)則，比如這里出現(xiàn)的sql_mode=only_full_group_by 問題，only_full_group_by 就是一個校驗規(guī)則，會規(guī)定分組查詢結果集不能有GROUP BY中沒有出現(xiàn)的列。
• 這個錯誤發(fā)生在mysql 5.7.5 版本及以上版本會出現(xiàn)的問題，mysql 5.7.5版本以上默認的 sql_mode 是包含 only_full_group_by ，這個配置嚴格執(zhí)行了"SQL92標準"，很多從5.6升級到5.7時，為了語法兼容，大部分都會選擇調整sql_mode，使其保持跟5.6一致，為了盡量兼容程序。

有兩種解決邏輯：

• 第一種：就是順應這個規(guī)則，別在GROUP BY是返回額外的列，當然，竟然SQL都這樣寫了肯定是需要額外的列了，我們看第二種方法
• 第二種：放開限制就行，將sql_mode 中的 only_full_group_by 剔除就行。

二、臨時解決（不需要重啟MySQL，適配所有版本和任意安裝方式的MySQL，重啟MySQL后會失效）

臨時解決這個問題可以不用重啟MySQL服務,，通過 set global 命令直接改就行。

1、查看當前 sql_mode （我這里MySQL的版本是8.0，其它版本也是一樣的做法）

登錄數(shù)據(jù)庫執(zhí)行下面命令可以看到當前包含的 sql_mode ，不同版本MySQL結果可能不同，但是我們的問題是因為 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，重新設置到系統(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ù)庫，修改完成之后最好重啟一下代碼服務，mysql服務不用重啟，如果不重啟可能還是會出現(xiàn)該問題，重啟后生效。

三、徹底解決（需要重啟MySQL才會生效）

要徹底解決這個問題就是將臨時解決方案中的 sql_mode 通過配置文件設置即可，建議是和臨時方案一起使用，先通過臨時方案讓業(yè)務服務可用，然后在配置文件也配置 sql_mode 下一次MySQL重啟后生效。

1、window 配置 sql_mode

window 上的 MySQL 配置文件默認是在 C:\Program Files\MySQL\MySQL Server 8.0\my-default.ini，打開這個 my-default.ini 文件找到[mysql]，在 [mysqld] 下設置 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的服務管理器，可以通過快捷鍵Win+R打開運行窗口，輸入services.msc后按回車鍵。
• 找到MySQL服務，服務名稱可能會叫MySQLxx（xx是版本信息），右鍵點擊，選擇停止服務。
• 等待MySQL服務停止后，再右鍵點擊MySQL服務，選擇啟動服務。
• 重啟MySQL服務后，可以通過以下命令檢查MySQL是否已正常運行

重啟好在MySQL客戶端執(zhí)行SHOW VARIABLES LIKE "sql_mode";查詢 sql_mode 是否已經(jīng)改變。

2、Linux 配置 sql_mode

Linux 上的 MySQL 配置文件默認是在 /etc/mysql/my.cnf，打開這個 my.cnf 文件找到[mysql]，在 [mysqld] 下設置 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 配置文件默認是在也是容器的 /etc/mysql/my.cnf 中，不過有點區(qū)別的是我們一般去直接改容器中的配置文件，而是會將額外配置文件路徑掛載出來，而且容器中是沒有 vi 這種工具不方便直接改配置，這里會對有做掛載和沒有做掛載的修改方式都說做說明。

使用docker安裝的MySQL的額外配置文件是在容器的 /etc/mysql/conf.d 目錄的，MySQL會讀取這個目錄下所以以 .cnf 結尾的文件，如果有對這個目錄做掛載最好，如果沒有做掛載也可用通過宿主機copy到容器內(nèi)部。

1、配置文件準備

我的配置文件目錄是掛載到了 /home/docker/mysql/conf.d，自己根據(jù)實際情況調整。

# 創(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、檢驗是否修改成功

# 進入容器
docker exec -it 容器ID/容器名稱 /bin/bash
# 連接MySQL
mysql -h127.0.0.1 -uroot -p
# 查看sql_mode是否被修改成功
SHOW VARIABLES LIKE "sql_mode";

到此這篇關于解決MySQL this is incompatible with sql_mode=only_full_group_by 問題的文章就介紹到這了,更多相關MySQL incompatible with內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！ 

最新評論