孤陋寡聞了，原來(lái) MySQL 還能這么寫？

作者：風(fēng)箏 2024-10-17 16:55:08

建議沒(méi)有特殊情況，還是打開(kāi)ONLY_FULL_GROUP_BY，這樣能保證你的查詢結(jié)果不會(huì)因?yàn)槟銓戝e(cuò) SQL 而出現(xiàn)莫名奇妙的數(shù)據(jù)，數(shù)據(jù)庫(kù)會(huì)及時(shí)給你拋出錯(cuò)誤，避免你對(duì)著一串 SQL 查錯(cuò)時(shí)一面懵。

最近給一個(gè)我從來(lái)沒(méi)碰過(guò)的老系統(tǒng)加點(diǎn)兒功能，本來(lái)連測(cè)試環(huán)境的數(shù)據(jù)庫(kù)一切都很順利，但是為了保證功能在生產(chǎn)數(shù)據(jù)上沒(méi)有問(wèn)題，就準(zhǔn)備把一部分生產(chǎn)數(shù)據(jù)搞到我本地環(huán)境上測(cè)一下。

結(jié)果，果然出現(xiàn)了問(wèn)題，代碼問(wèn)題就是這樣，總在不經(jīng)意間來(lái)到。

依我我知，想要不出現(xiàn)代碼問(wèn)題，最好的方式就是——不寫代碼！

出現(xiàn)問(wèn)題，咱就解決啊，我一看日志控制臺(tái)一大堆紅色報(bào)錯(cuò)，不對(duì)啊，這都不是我剛加的功能報(bào)出來(lái)的，這樣的話，我就放心了。

摘出來(lái)一條錯(cuò)誤信息是下面這樣的，是 SQL 查詢語(yǔ)句報(bào)錯(cuò)了，報(bào)錯(cuò)信息很明顯，就是說(shuō)GROUP BY 后面的條件沒(méi)有在前面的 SELECT Columns 列表里。

一看其中有兩個(gè)關(guān)鍵點(diǎn)：

GROUP BY
sql_mode=only_full_group_by

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.001000s

我把 SQL 簡(jiǎn)化了一下就是下面這個(gè)樣子：

select u.id,u.age from user u GROUP BY u.province;

一看這條 SQL，我心想，這SQL 本身就有問(wèn)題啊，怎么就在系統(tǒng)中呆了這么長(zhǎng)時(shí)間，但是在測(cè)試和生產(chǎn)環(huán)境確實(shí)就是正常運(yùn)行的。

我本地一直用MySQL5.7，再看測(cè)試和生產(chǎn)數(shù)據(jù)庫(kù)，也是 5.7，就是小版本不太一樣，按往常經(jīng)驗(yàn)，小版本應(yīng)該不會(huì)有這么大影響。

那肯定就是配置的問(wèn)題，那肯定就是報(bào)錯(cuò)信息中提到的 sql_mode=only_full_group_by 這個(gè)，原諒我孤陋寡聞了，用了 MySQL 這么久，從來(lái)沒(méi)聽(tīng)過(guò)這玩意，而且用 GROUP BY就是為了分組聚合，GROUP BY后面的條件要出現(xiàn)在 SELECT 列表里不是很正常嗎，除非有兩個(gè)列有同樣的作用，比如一個(gè)名稱，一個(gè)編碼，用編碼分組，顯示名稱。要不然分組的意義在哪里呢？

但是系統(tǒng)已經(jīng)運(yùn)行了很長(zhǎng)時(shí)間了，那這 SQL 一定有他存在的意義，不管那么多了，看問(wèn)題就好了。

sql_mode

然后我給自己科普了一下 sql_mode。

sql_mode 是 MySQL 的一個(gè)系統(tǒng)變量，用來(lái)控制 MySQL 服務(wù)器的 SQL 語(yǔ)法和行為的處理方式。通過(guò)配置不同的 sql_mode 值，MySQL 可以在 SQL 語(yǔ)法檢查、數(shù)據(jù)完整性約束、以及查詢處理等方面進(jìn)行不同的操作。

總之，就是 MySQL 會(huì)根據(jù)這個(gè)配置的內(nèi)容，來(lái)靈活的進(jìn)行語(yǔ)法檢查、數(shù)據(jù)約束等操作，加入的變量越多，控制的就越嚴(yán)格。

發(fā)現(xiàn)從 MySQL 5.7 開(kāi)始，sql_mode 加了很多變量，ONLY_FULL_GROUP_BY就在其中。

使用 SELECT @@sql_mode;可以查詢數(shù)據(jù)庫(kù)中 sql_mode 配置的變量有哪些，這是 5.7 版本的默認(rèn)配置。

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

既然是變量，就是可以改的，所以，這些變量可以動(dòng)態(tài)的增減，或者索性全部去掉。

那肯定就是測(cè)試和生產(chǎn)環(huán)境改了這個(gè)配置了，上去一查，果不其然，那叫一個(gè)干凈。后來(lái)問(wèn)之前的同事，了解到之前用的是更早的 MySQL 版本，后來(lái)統(tǒng)一升級(jí)到了 5.7，然后發(fā)現(xiàn)這個(gè)問(wèn)題，所以改了配置。

ONLY_FULL_GROUP_BY

別的不說(shuō)，只說(shuō) ONLY_FULL_GROUP_BY，當(dāng)數(shù)據(jù)庫(kù)中啟用了 ONLY_FULL_GROUP_BY 模式后，就要求在 GROUP BY 查詢中，SELECT 子句中的每一列都必須要么出現(xiàn)在 GROUP BY 子句中，要么應(yīng)用聚合函數(shù)（如 COUNT()、SUM()、MAX()、MIN() 等）。

這個(gè)例子中就是，province這個(gè)字段沒(méi)有在前面 SELECT 的字段列表中。我從剛用 MySQL 時(shí)一直都是按照 GROUP BY后面的列必須在前面的查詢列中來(lái)做的，沒(méi)想到這個(gè)還能改。

select u.id,u.age from user u GROUP BY u.province;

這種不行，下面這個(gè)也不行，因?yàn)?SELECT 查詢列表中的 u.id 不在 GROUP BY 后面的條件中

select u.id,u.age from user u GROUP BY u.age;

改成下面這樣才行

select u.id,u.age from user u GROUP BY u.age,u.id;
--  或者
select u.age from user u GROUP BY u.age;

或者，還有一種情況，可以允許 SELECT 中存在 GROUP BY 后面沒(méi)有的列，就是加聚合函數(shù)。

這應(yīng)該是最常規(guī)的用法了。

select max(u.id),u.age from user u GROUP BY u.age;

除了影響 GROUP BY外，還會(huì)影響 ORDER BY，看下面這條語(yǔ)句，當(dāng)開(kāi)啟 ONLY_FULL_GROUP_BY后，會(huì)報(bào)錯(cuò)

SELECT DISTINCT
	b.title,
	b.create_time 
FROM
	b_user b 
ORDER BY
	b.create_time DESC,
	b.update_time DESC

報(bào)錯(cuò)信息：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b.update_time DESC' at line 24, Time: 0.001000s

因?yàn)閡pdate_time字段不在 SELECT 后面，當(dāng)然這還是因?yàn)榧恿?DISTINCT。當(dāng)關(guān)閉 ONLY_FULL_GROUP_BY后，就能正常執(zhí)行了。