一、錯(cuò)誤原因分析

出現(xiàn)這個(gè)問(wèn)題是因?yàn)?，?duì)于GROUP BY操作，如果在SELECT中的列，沒(méi)有在GROUP BY中出現(xiàn)，那么這個(gè)SQL是不合法的，因?yàn)榱胁辉贕ROUP BY從句中。
也就是說(shuō)如果我分組查詢，比如我表里有三個(gè)字段 id、name、type，我根據(jù)type分組查詢，那么結(jié)果集里就不能包含name字段。

# 錯(cuò)誤查詢 會(huì)出現(xiàn)this is incompatible with sql_mode=only_full_group_by
SELECT `name`,`type` FROM user_01 GROUP BY `type`;

# 正確查詢 不會(huì)出現(xiàn)問(wèn)題
SELECT `type` FROM user_01 GROUP BY `type`;

二、原理分析&解決方案分析

• 要想解決這個(gè)問(wèn)題首先要知道MySQL的 sql_mode 是什么，sql_mode 是個(gè)很容易被忽視的變量，sql_mode 是規(guī)范一些數(shù)據(jù)庫(kù)校驗(yàn)規(guī)則，比如這里出現(xiàn)的sql_mode=only_full_group_by 問(wèn)題，only_full_group_by 就是一個(gè)校驗(yàn)規(guī)則，會(huì)規(guī)定分組查詢結(jié)果集不能有GROUP BY中沒(méi)有出現(xiàn)的列。
• 這個(gè)錯(cuò)誤發(fā)生在mysql 5.7.5 版本及以上版本會(huì)出現(xiàn)的問(wèn)題，mysql 5.7.5版本以上默認(rèn)的 sql_mode 是包含 only_full_group_by ，這個(gè)配置嚴(yán)格執(zhí)行了"SQL92標(biāo)準(zhǔn)"，很多從5.6升級(jí)到5.7時(shí)，為了語(yǔ)法兼容，大部分都會(huì)選擇調(diào)整sql_mode，使其保持跟5.6一致，為了盡量兼容程序。

有兩種解決邏輯：

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

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

臨時(shí)解決這個(gè)問(wèn)題可以不用重啟MySQL服務(wù),，通過(guò) set global 命令直接改就行。

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

登錄數(shù)據(jù)庫(kù)執(zhí)行下面命令可以看到當(dāng)前包含的 sql_mode ，不同版本MySQL結(jié)果可能不同，但是我們的問(wèn)題是因?yàn)?only_full_group_by 引起的，我們看看里面是否有這個(gè)。

SHOW VARIABLES LIKE "sql_mode";

這里可以看到，我的 sql_mode 中第一個(gè)就是 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、臨時(shí)修改 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ù)庫(kù)，修改完成之后最好重啟一下代碼服務(wù)，mysql服務(wù)不用重啟，如果不重啟可能還是會(huì)出現(xiàn)該問(wèn)題，重啟后生效。

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

要徹底解決這個(gè)問(wèn)題就是將臨時(shí)解決方案中的 sql_mode 通過(guò)配置文件設(shè)置即可，建議是和臨時(shí)方案一起使用，先通過(guò)臨時(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，打開(kāi)這個(gè) my-default.ini 文件找到[mysql]，在 [mysqld] 下設(shè)置 sql_mode ，將通過(guò)SHOW VARIABLES LIKE "sql_mode";查詢出來(lái)的 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：

• 打開(kāi)Windows的服務(wù)管理器，可以通過(guò)快捷鍵Win+R打開(kāi)運(yùn)行窗口，輸入services.msc后按回車鍵。
• 找到MySQL服務(wù)，服務(wù)名稱可能會(huì)叫MySQLxx（xx是版本信息），右鍵點(diǎn)擊，選擇停止服務(wù)。
• 等待MySQL服務(wù)停止后，再右鍵點(diǎn)擊MySQL服務(wù)，選擇啟動(dòng)服務(wù)。
• 重啟MySQL服務(wù)后，可以通過(guò)以下命令檢查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，打開(kāi)這個(gè) my.cnf 文件找到[mysql]，在 [mysqld] 下設(shè)置 sql_mode ，將通過(guò)SHOW VARIABLES LIKE "sql_mode";查詢出來(lái)的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。

# 打開(kāi)/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 中，不過(guò)有點(diǎn)區(qū)別的是我們一般去直接改容器中的配置文件，而是會(huì)將額外配置文件路徑掛載出來(lái)，而且容器中是沒(méi)有 vi 這種工具不方便直接改配置，這里會(huì)對(duì)有做掛載和沒(méi)有做掛載的修改方式都說(shuō)做說(shuō)明。

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

1、配置文件準(zhǔn)備

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

# 創(chuàng)建一個(gè)配置文件
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、如果沒(méi)有做掛載需要將這個(gè)配置文件拷貝到容器內(nèi)（有做掛載跳過(guò)）

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 問(wèn)題的文章就介紹到這了,更多相關(guān)MySQL incompatible with內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論