MySQL 是否通過允許選擇不屬於 group by 子句的列來打破標準？

我習慣了包括 SQL Server 在內的 Microsoft 技術。今天我遇到了一個問答，其中引用了 MySQL 文件中的以下段落：

標準 SQL會拒絕您的查詢，因為您不能在聚合查詢中選擇不屬於 GROUP BY 子句的非聚合欄位。MySQL 擴展了 GROUP BY 的使用，以便選擇列表可以引用未在 GROUP BY 子句中命名的非聚合列。這意味著前面的查詢在 MySQL 中是合法的。您可以使用此功能通過避免不必要的列排序和分組來獲得更好的性能。但是，這主要在每個未在 GROUP BY 中命名的非聚合列中的所有值對於每個組都相同時很有用。伺服器可以從每個組中自由選擇任何值，因此除非它們相同，否則選擇的值是 不確定的。

MySQL是否通過允許這樣做打破了標準？如何？允許這樣做的結果是什麼？

標準 SQL 會拒絕您的查詢，因為您不能在聚合查詢中選擇不屬於 GROUP BY 子句的****非聚合欄位

這是正確的，直到 1992 年。

但從2003 年及以後，這顯然是錯誤的。

來自 SQL-2003 標準，6IWD6-02-Foundation-2011-01.pdf，來自 http://www.wiscorp.com/，第 7.12 段（查詢規範），第 398 頁：

17. 如果 T 是一個分組表，則令 G 是 T 的分組列的集合。在 ((select list)) 中包含的每個 ((value expression)) 中，引用 T 的列的每個列引用都應引用某個列 C在功能上依賴於 G或應包含在聚合查詢為 QS 的 ((set function specification)) 的聚合參數中

---

現在 MYSQL 已經實現了這個特性，它不僅允許在功能上依賴於分組列的列**，而且允許所有列**。這會給不了解分組如何工作的使用者帶來一些問題，並在他們不期望的地方獲得不確定的結果。

但是你說 MySQL 添加了一個與 SQL 標準衝突的特性是對的（儘管你似乎認為這是錯誤的原因）。這並不完全準確，因為他們添加了 SQL 標準功能，但不是以最好的方式（更像是簡單的方式），但它確實與最新標準相衝突。

為了回答您的問題，我認為這個 MySQL 功能（擴展）的原因是符合最新的 SQL 標準（2003+）。為什麼他們選擇以這種方式實現（不完全兼容），我們只能推測。

正如@Quassnoi 和@Johan 用範例回答的那樣，這主要是性能和可維護性問題。但是不能輕易地將 RDBMS 更改為足夠聰明（不包括天網）以辨識功能相關的列，因此 MySQL 開發人員做出了選擇：

我們（MySQL）為您（MySQL 使用者）提供 SQL-2003 標準中的此功能。它提高了某些GROUP BY查詢的速度，但有一個問題。您必須小心（而不是 SQL 引擎），以便SELECT和HAVING列表中的列在功能上依賴於這些GROUP BY列。如果沒有，您可能會得到不確定的結果。

如果要禁用它，可以設置sql_mode為**ONLY_FULL_GROUP_BY**。

這一切都在MySQL 文件中：Extensions to GROUP BY(5.5) - 雖然不是在上面的措辭中，而是在你的引用中（他們甚至忘記提到它偏離標準 SQL-2003 而不是標準 SQL-92）。我認為這種選擇在所有軟體中都很常見，包括其他 RDBMS。它們是出於性能、向後兼容性和許多其他原因而製作的。Oracle 有著名'' is the same as NULL的例子，SQL-Server 可能也有一些。

還有 Peter Bouman 的這篇部落格文章，其中捍衛了 MySQL 開發人員的選擇：揭穿 GROUP BY 神話。

2011 年，正如@Mark Byers在評論中告訴我們的（在 DBA.SE 的一個相關問題中），**PostgreSQL 9.1 添加了一個**為此目的而設計的新功能（發布日期：2011 年 9 月）。它比 MySQL 的實現更嚴格，更接近標準。

後來，2015 年 MySQL 宣佈在 5.7 版本中改進了行為以符合標準並真正辨識功能依賴（甚至比 Postgres 實現更好）。文件：MySQL 處理GROUP BY(5.7)和 Peter Bouman 的另一篇博文：MySQL 5.7.5：GROUP BY尊重功能依賴！

簡短的回答

這是一個速度黑客

預設情況下啟用，但可以使用此設置禁用： https ://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

長答案 非標準速記 group by 子句的原因是它是一種速度黑客。

MySQL 讓程序員確定所選欄位在功能上是否依賴於 group by 子句。

數據庫不做任何測試，只是選擇它找到的第一個結果作為欄位的值。

這導致相當大的加速。

考慮這段程式碼：

SELECT f1, f2, f3, f4 FROM t1 GROUP BY f2   
-- invalid in most SQL flavors, valid in MySQL  

MySQL 只會選擇它找到的第一個值，花費最少的時間。

f1,f3,f4 將來自同一行，但如果涉及多個具有連接的表，則此關係將分崩離析。

為了在 SQL-server 中做同樣的事情，你必須做

SELECT MIN(f1), f2, MIN(f3), MIN(f4) FROM t1 GROUP BY f2  
-- valid SQL, but really a hack

DB 現在必須檢查所有結果以找到最小值，喘氣和喘氣。

f1、f3、f4 很可能彼此沒有關係，並且不會來自同一行。

但是，如果您這樣做：

SELECT id as `primary_key`, count(*) as rowcount, count(f2) as f2count, f2, f3, f4 
FROM t1 
GROUP BY id

所有其餘欄位將在功能上依賴於id.

Rowcount 將始終為 1，f2count 將為 0（如果 f2 為空）或 1。

在 1-n 配置中，涉及大量表的連接，如下所示：

例子：

網站 1 -> n 主題 1 -> n 主題 1 -> n 文章 1 -> 1 人。

你做了一個涉及所有表的複雜選擇，然後只做一個GROUP BY posts.id

顯然所有其他欄位在功能上都依賴於posts.id（並且只依賴於posts.id）。

因此，在 group by 子句中列出更多欄位或強制您使用聚合函式是沒有意義的。

為了加快速度。MySQL 不會強迫你這樣做。

但是您確實需要了解函式依賴的概念以及表中的關係以及您編寫的連接，因此它給程序員帶來了很大的負擔。

但是使用：

SELECT 
 posts.id, MIN(posts.f2)
 ,MIN(threads.id), min(threads.other)
 ,MIN(topics.id), ....
 ,MIN(website.id), .....
 ,MIN(Person.id), ...
FROM posts p
INNER JOIN threads t on (p.thread_id = t.id)
INNER JOIN topic to on (t.topic_id = to.id)
INNER JOIN website w ON (w.id = to.website_id)
INNER JOIN person pe ON (pe.id = p.person_id)
GROUP BY posts.id   //NEVER MIND THE SYNTAX ERROR WITH THE ALIASES

給程序員帶來了完全相同的精神負擔。

引用自：https://dba.stackexchange.com/questions/294989