MySQL8.x group_by報錯解決方案

在我們使用MySQL的時候總是會遇到各種各樣的報錯，讓人頭痛不已。

其中有一種報錯，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版本未報錯，但是服務(wù)器上的版本報錯：

• 本機版本：

• 服務(wù)器版本：

不知道是8.x哪個版本之后SQL_MODE解除了限制，還是安裝與解壓版的的區(qū)別，目前發(fā)現(xiàn)的問題是服務(wù)器解壓版應(yīng)該是ini未設(shè)置SQL_MODE模式。

出現(xiàn)這樣的報錯，并不是因為你的代碼寫得不好，而是因為在MySQL 5.7后，MySQL默認(rèn)開啟了SQL_MODE嚴(yán)格模式，對數(shù)據(jù)進行嚴(yán)格校驗。如果代碼中含有g(shù)roup by聚合操作，那么select中的列，除了使用聚合函數(shù)之外的，如max()、min()等，都必須出現(xiàn)在group by中。

比如說，出現(xiàn)下面這種情況，就會報錯：

select id,aa from tab_test group by aa

如果改成這個樣子：

select id,aa from tab_test group by id,aa

或者這個樣子：

select aa from tab_test group by aa

就不會報錯了。

當(dāng)然，這樣子隨意的改動代碼，我們可能就無法得到想要的信息了。

我們可以通過以下四種方法，解決該問題：

方法一：直接修改數(shù)據(jù)庫配置

首先，打開數(shù)據(jù)庫，輸入

select @@global.sql_mode;

這個時候，就會返回得到以下的信息：（不同電腦返回的信息可能不同）

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)我們再一次重新啟動數(shù)據(jù)庫時，可能會恢復(fù)原樣，還是會出現(xiàn)ONLY_FULL_GROUP_BY的報錯，這就需要我們再一次修改數(shù)據(jù)庫配置。

那有沒有可以永久生效的辦法呢？當(dāng)然有！參考方法二，就可以使修改數(shù)據(jù)庫配置永久生效！

方法二：修改數(shù)據(jù)庫配置（永久生效）

修改配置文件my.ini

在[mysqld]模塊下新增一行配置：

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

運行后重啟，即可生效

方法三：使用 any_value() 或 group_concat()

1. any_value()：將分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回數(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)用逗號隔開作為返回數(shù)據(jù),如下圖：

方法四：開動腦筋，修改代碼

舉個例子，在?？偷牡?06題中，就出現(xiàn)了類似的情況

SQL206 獲取每個部門中當(dāng)前員工薪水最高的相關(guān)信息

題目如下：

示例：

如果我們不用上面的方法，直接用下面這種方式查詢，很明顯會出現(xiàn) ONLY_FULL_GROUP_BY 的報錯：

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，一張表為每個部門最高員工薪資表 表b。然后通過表連接 on d.emp_no = s.emp_no 使這兩張表結(jié)合，通過on a.salary = b.salary使薪資統(tǒng)一，即這兩張表連接后的表的薪資為每個部門的最高員工薪資。最后，再通過一次查詢得到每個部門中當(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é)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論