MySQL報錯sql_mode=only_full_group_by的問題解決
報錯信息
DataGrip 報錯還原
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx庫.xx表.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Navicat 報錯還原
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘school.student_100w.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
報錯原因
MySQL 5.7后,MySQL的 SQL_MODE
配置項會默認(rèn)包含 only_full_group_by
模式,這個配置會嚴(yán)格執(zhí)行SQL92標(biāo)準(zhǔn)。如果代碼中含有g(shù)roup by聚合操作,那么select中的列,除了使用聚合函數(shù)之外的,如max()、min()等,都必須出現(xiàn)在group by中。
要求:從學(xué)生表中,根據(jù)班級編號,分組查詢班級編號、班級名稱。
報錯:SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段不統(tǒng)一。
# SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段不統(tǒng)一,會報錯。 SELECT class_number, class_name FROM student GROUP BY class_number;
調(diào)整:將SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一。
# SELECT 查詢字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一后就不會報錯。 SELECT class_number, class_name FROM student GROUP BY class_number, class_name ;
# SELECT 查詢字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一后就不會報錯。 SELECT class_number FROM student GROUP BY class_number;
解決方案
查看當(dāng)前 sql mode
執(zhí)行 SQL
語句,查看當(dāng)前的 sql_mode
。
SELECT @@global.sql_mode;
注意默認(rèn)的列寬可能無法直接查看全部內(nèi)容,容易造成誤解,需要手動拉伸列寬查看。
拉伸列寬后可查看全部內(nèi)容,(不同版本的 MySQL
默認(rèn)值可能不同)。
方案一:臨時解決
排除當(dāng)前 sql_mode
中 ONLY_FULL_GROUP_BY
值,復(fù)制剩下的值。執(zhí)行設(shè)置語句:
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
但是如果當(dāng)前數(shù)據(jù)庫重啟后,還是回歸默認(rèn)設(shè)置,即包含 ONLY_FULL_GROUP_BY
模式。
方案二:永久解決
windows系統(tǒng),直接修改 my.ini
配置文件。
在 [mysqld]
模塊下新增一行配置:
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
注意:
- 不能有多余的符號,我之前在結(jié)尾加了分號導(dǎo)致重啟失敗。
- 點擊保存文件,不要直接關(guān)掉。 不然設(shè)置丟失,是不會生效的。
Linux系統(tǒng),思路是一樣的,都是找配置文件修改配置。但是不同Mysql版本,路徑和配置文件名稱可能不同:大致可能是 /etc/my.cnf
或者 /mysql.conf
之類的名稱。
數(shù)據(jù)庫重啟后生效。
方案三:使用 any_value() 或 group_concat()
- any_value():
- 將分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回數(shù)據(jù)。 (any_value()函數(shù)就是MySQL提供的用來抑制ONLY_FULL_GROUP_BY值被拒絕的)
SELECT class_number, any_value(class_name) FROM student GROUP BY class_number;
實際使用效果如下:
- group_concat():
- 將分到同一組的數(shù)據(jù)默認(rèn)用逗號隔開作為返回數(shù)據(jù)。
SELECT class_number, group_concat(class_name) FROM student GROUP BY class_number;
實際使用效果如下:
方案四:調(diào)整實現(xiàn)思路,避開 GROUP BY 使用
通過對SQL的查詢思路調(diào)整,代碼邏輯調(diào)整等,部分場景是可以繞開對 GROUP BY 關(guān)鍵字篩選依賴的。這個需要結(jié)合實際業(yè)務(wù)場景,自己去思考調(diào)整了。
到此這篇關(guān)于MySQL報錯sql_mode=only_full_group_by的問題解決的文章就介紹到這了,更多相關(guān)MySQL sql_mode=only_full_group_by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
CentOS 7.0如何啟動多個MySQL實例教程(mysql-5.7.21)
這篇文章主要給大家介紹了關(guān)于CentOS 7.0如何啟動多個MySQL實例(mysql-5.7.21)的相關(guān)資料,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起看看吧。2018-03-03MySQL普通表轉(zhuǎn)換為分區(qū)表實戰(zhàn)指南
本文將詳細指導(dǎo)新手開發(fā)者如何將MySQL中的普通表轉(zhuǎn)換為分區(qū)表,分區(qū)表在處理龐大數(shù)據(jù)集時展現(xiàn)出顯著的性能優(yōu)勢,不僅能大幅提升查詢速度,還能有效簡化數(shù)據(jù)維護工作,文中有詳細的代碼示例供大家參考,需要的朋友可以參考下2024-06-06MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決
本文主要介紹了MySQL隱式類型轉(zhuǎn)換導(dǎo)致索引失效的解決,文中通過示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下2021-08-08MySQL字段類型與Java實體類類型對應(yīng)轉(zhuǎn)換關(guān)系詳解
這篇文章主要介紹了MySQL字段類型與Java實體類類型對應(yīng)轉(zhuǎn)換關(guān)系,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06利用frm和ibd文件恢復(fù)mysql表數(shù)據(jù)的詳細過程
總是遇到mysql服務(wù)意外斷開之后導(dǎo)致mysql服務(wù)無法正常運行的情況,使用Navicat工具查看能夠看到里面的庫和表,但是無法獲取數(shù)據(jù)記錄,提示數(shù)據(jù)表不存在,所以本文給大家介紹了利用frm和ibd文件恢復(fù)mysql表數(shù)據(jù)的詳細過程,需要的朋友可以參考下2024-04-04