關(guān)于MySQL8.x group_by報(bào)錯(cuò)的4種解決方案
MySQL8.x group_by報(bào)錯(cuò)解決方案
在我們使用MySQL的時(shí)候總是會(huì)遇到各種各樣的報(bào)錯(cuò),讓人頭痛不已。
其中有一種報(bào)錯(cuò),sql_mode=only_full_group_by,十分常見,每次都是老長的一串出現(xiàn),然后帶走你所有的好心情,如:
LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tab_test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.063 sec
由于本地安裝得mysql版本未報(bào)錯(cuò),但是服務(wù)器上的版本報(bào)錯(cuò):
- 本機(jī)版本:
- 服務(wù)器版本:
不知道是8.x哪個(gè)版本之后SQL_MODE解除了限制,還是安裝與解壓版的的區(qū)別,目前發(fā)現(xiàn)的問題是服務(wù)器解壓版應(yīng)該是ini未設(shè)置SQL_MODE模式。
出現(xiàn)這樣的報(bào)錯(cuò),并不是因?yàn)槟愕拇a寫得不好,而是因?yàn)樵贛ySQL 5.7后,MySQL默認(rèn)開啟了SQL_MODE嚴(yán)格模式,對數(shù)據(jù)進(jìn)行嚴(yán)格校驗(yàn)。如果代碼中含有g(shù)roup by聚合操作,那么select中的列,除了使用聚合函數(shù)之外的,如max()、min()等,都必須出現(xiàn)在group by中。
比如說,出現(xiàn)下面這種情況,就會(huì)報(bào)錯(cuò):
select id,aa from tab_test group by aa
如果改成這個(gè)樣子:
select id,aa from tab_test group by id,aa
或者這個(gè)樣子:
select aa from tab_test group by aa
就不會(huì)報(bào)錯(cuò)了。
當(dāng)然,這樣子隨意的改動(dòng)代碼,我們可能就無法得到想要的信息了。
我們可以通過以下四種方法,解決該問題:
方法一:直接修改數(shù)據(jù)庫配置
首先,打開數(shù)據(jù)庫,輸入
select @@global.sql_mode;
這個(gè)時(shí)候,就會(huì)返回得到以下的信息:(不同電腦返回的信息可能不同)
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
如果里面包含 ONLY_FULL_GROUP_BY,那么就重新設(shè)置,在數(shù)據(jù)庫中輸入以下代碼,去掉ONLY_FULL_GROUP_BY即可:
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
但是,當(dāng)我們再一次重新啟動(dòng)數(shù)據(jù)庫時(shí),可能會(huì)恢復(fù)原樣,還是會(huì)出現(xiàn)ONLY_FULL_GROUP_BY的報(bào)錯(cuò),這就需要我們再一次修改數(shù)據(jù)庫配置。
那有沒有可以永久生效的辦法呢?當(dāng)然有!參考方法二,就可以使修改數(shù)據(jù)庫配置永久生效!
方法二:修改數(shù)據(jù)庫配置(永久生效)
修改配置文件my.ini
在[mysqld]模塊下新增一行配置:
sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
運(yùn)行后重啟,即可生效
方法三:使用 any_value() 或 group_concat()
1. any_value():將分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回?cái)?shù)據(jù)。 (any_value()函數(shù)就是MySQL提供的用來抑制ONLY_FULL_GROUP_BY值被拒絕的)
select a,any_value(b) from test group by a
2. group_concat():將分到同一組的數(shù)據(jù)默認(rèn)用逗號(hào)隔開作為返回?cái)?shù)據(jù),如下圖:
方法四:開動(dòng)腦筋,修改代碼
舉個(gè)例子,在??偷牡?06題中,就出現(xiàn)了類似的情況
SQL206 獲取每個(gè)部門中當(dāng)前員工薪水最高的相關(guān)信息
題目如下:
示例:
如果我們不用上面的方法,直接用下面這種方式查詢,很明顯會(huì)出現(xiàn) ONLY_FULL_GROUP_BY 的報(bào)錯(cuò):
select dept_no,d.emp_no,max(s.salary) from dept_emp d join salaries s on d.emp_no = s.emp_no group by d.dept_no
我們可以換一種思路:
首先查詢得到2張表,一張表為員工薪資表 表a,一張表為每個(gè)部門最高員工薪資表 表b。然后通過表連接 on d.emp_no = s.emp_no 使這兩張表結(jié)合,通過on a.salary = b.salary使薪資統(tǒng)一,即這兩張表連接后的表的薪資為每個(gè)部門的最高員工薪資。最后,再通過一次查詢得到每個(gè)部門中當(dāng)前員工薪水最高的相關(guān)信息。
select a.dept_no, a.emp_no, b.salary from (select d.dept_no, s.emp_no, s.salary from dept_emp as d join salaries as s on d.emp_no = s.emp_no ) as a join (select d.dept_no, max(s.salary) as salary from dept_emp as d join salaries as s on d.emp_no = s.emp_no group by d.dept_no ) as b on a.salary = b.salary and a.dept_no = b.dept_no order by a.dept_no
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
MySQL select、insert、update批量操作語句代碼實(shí)例
這篇文章主要介紹了MySQL select、insert、update批量操作語句代碼實(shí)例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-03-03借助PHP的mysql_query()函數(shù)來創(chuàng)建MySQL數(shù)據(jù)庫的教程
這篇文章主要介紹了借助PHP的mysql_query()函數(shù)來創(chuàng)建MySQL數(shù)據(jù)庫的教程,將函數(shù)配合CREATE DATABASE語句使用,需要的朋友可以參考下2015-12-12mysql數(shù)據(jù)校驗(yàn)過程中的字符集問題處理
在日常應(yīng)用中,我們經(jīng)常會(huì)遇到在不同的字符集的數(shù)據(jù)庫直接進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出操作,針對這個(gè)問題,我們來進(jìn)行討論下2014-05-05MySQL統(tǒng)計(jì)函數(shù)GROUP_CONCAT使用陷阱分析
這篇文章主要介紹了MySQL統(tǒng)計(jì)函數(shù)GROUP_CONCAT使用中的陷阱,結(jié)合實(shí)例形式分析了GROUP_CONCAT用于統(tǒng)計(jì)時(shí)的長度限制問題與相關(guān)注意事項(xiàng),需要的朋友可以參考下2016-06-06golang實(shí)現(xiàn)mysql數(shù)據(jù)庫備份的操作方法
這篇文章主要介紹了golang實(shí)現(xiàn)mysql數(shù)據(jù)庫備份的操作方法,非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-06-06mysqldump命令導(dǎo)入導(dǎo)出數(shù)據(jù)庫方法與實(shí)例匯總
這篇文章主要介紹了mysqldump命令導(dǎo)入導(dǎo)出數(shù)據(jù)庫方法與實(shí)例匯總的相關(guān)資料,需要的朋友可以參考下2015-10-10Mysql auto_increment 重新計(jì)數(shù)(讓id從1開始)
當(dāng)清空一個(gè)表的時(shí)候,重新插入數(shù)據(jù),發(fā)現(xiàn)auto_increment屬性的字段計(jì)數(shù)不是從1開始的時(shí)候,可以使用以下命令2012-12-12