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)格模式，對(duì)數(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)我們?cè)僖淮沃匦聠?dòng)數(shù)據(jù)庫時(shí)，可能會(huì)恢復(fù)原樣，還是會(huì)出現(xiàn)ONLY_FULL_GROUP_BY的報(bào)錯(cuò)，這就需要我們?cè)僖淮涡薷臄?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è)參考，也希望大家多多支持腳本之家。

最新評(píng)論