group by是否會用到索引_MySQL：為什麼查詢清單中多了它，GROUP BY語句就會報錯呢？

事前準備

為了故事的順利發展，我們先得建一個表：

CREATE TABLE student_score (
  number INT(11) NOT NULL,
  name VARCHAR(30) NOT NULL,
  subject VARCHAR(30) NOT NULL,
  score TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (number,subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;           

這個

student_score

表是用來存儲學生成績的，我們為這個條填充一些資料，填充後的效果就像這樣：

mysql> SELECT * FROM student_score;
+----------+-----------+-----------------------------+-------+
| number   | name      | subject                     | score |
+----------+-----------+-----------------------------+-------+
| 20180101 | 杜子騰    | 母豬的産後護理              |    78 |
| 20180101 | 杜子騰    | 論薩達姆的戰争準備          |    88 |
| 20180102 | 杜琦燕    | 母豬的産後護理              |   100 |
| 20180102 | 杜琦燕    | 論薩達姆的戰争準備          |    98 |
| 20180103 | 範統      | 母豬的産後護理              |    59 |
| 20180103 | 範統      | 論薩達姆的戰争準備          |    61 |
| 20180104 | 史珍香    | 母豬的産後護理              |    55 |
| 20180104 | 史珍香    | 論薩達姆的戰争準備          |    46 |
+----------+-----------+-----------------------------+-------+
8 rows in set (0.00 sec)           

GROUP BY是在幹什麼？

我們知道

MySQL

提供了一系列的

聚集函數

，諸如：

• COUNT

  ：統計記錄數。

• MAX

  ：查詢某列的最大值。

• MIN

  ：查詢某列的最小值。

• SUM

  ：某列資料的累加總和。

• AVG

  ：某列資料的平均數。

比方說我們想檢視一下

student_score

表中所有人成績的平均數就可以這麼寫：

mysql> SELECT AVG(score) FROM student_score;
+------------+
| AVG(score) |
+------------+
|    73.1250 |
+------------+
1 row in set (0.00 sec)           

如果我們隻想檢視

《母豬的産後護理》

這個科目的平均成績，那加個

WHERE

子句就好了：

mysql> SELECT AVG(score) FROM student_score WHERE subject = '母豬的産後護理';
+------------+
| AVG(score) |
+------------+
|    73.0000 |
+------------+
1 row in set (0.00 sec)           

同理，我們也可以單獨檢視

《論薩達姆的戰争準備》

這門課程的平均成績：

mysql> SELECT AVG(score) FROM student_score WHERE subject = '論薩達姆的戰争準備';
+------------+
| AVG(score) |
+------------+
|    73.2500 |
+------------+
1 row in set (0.00 sec)           

這時候問題來了，如果這個

student_score

表中存儲了20門科目的成績資訊，那我們怎麼單獨的得到這20門課程的平均成績呢？單獨寫20個查詢語句？那要是有100門課呢？

很顯然，不能傻兮兮的寫一百個語句，設計

MySQL

的大叔給我們提供了

分組

的概念。我們可以按照某個列将表中的資料進行分組，比方說我們現在按照

subject

列對表中資料進行分組，那麼所有的記錄就會被分成2組，如圖所示：

讓

MySQL

産生這樣子的分組的語句就是

GROUP BY

子句，我們隻要在

GROUP BY

後邊把需要分組的列寫上就好，然後在查詢清單處就可以針對每一個分組來寫相應的聚集函數去統計該分組，就像這樣：

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母豬的産後護理              |    73.0000 |
| 論薩達姆的戰争準備          |    73.2500 |
+-----------------------------+------------+
2 rows in set (0.00 sec)           

報錯和解決

可以從上邊帶有

GROUP BY

子句的查詢語句中看出來，我們隻在查詢清單處放了分組列subject以及對該分組中的記錄調用的聚集函數AVG，那如果我們把不是分組列的字段也放到查詢清單中會出現啥情況：

mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dahaizi.student_score.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql>           

可以看到報錯了，為啥會報錯呢？回想一下我們使用

GROUP BY

子句的初衷，我們隻是想把記錄分為若幹組，然後再對各個組分别調用聚集函數去做一些統計工作。本例中的查詢清單處放置了既非分組列、又非聚集函數的

name

列，那我們想表達啥意思呢？從各個分組中的記錄中取一個記錄的

name

列？該取哪條記錄為好呢？比方說對于

'母豬的産後護理'

這個分組中的記錄來說，

name

列的值應該取

杜子騰

，還是

杜琦燕

，還是

範統

，還是

史珍香

呢？這個我們也不知道，是以把非分組列放到查詢清單中會引起争議，導緻結果不确定，是以設計

MySQL

的大叔才會為上述語句報錯。

不過有的同學會說，假如分組後的某個分組的某個非分組列的值都一樣，那我把該非分組列加入到查詢清單中也沒啥問題呀。比方說按照

subject

列進行分組後，假如在

'母豬的産後護理'

的分組中各條記錄的

name

列的值都相同，在

'論薩達姆的戰争準備'

的分組中各條記錄的

name

列的值也都相同，那麼我們把

name

列放在查詢清單中也沒啥問題。可能設計

MySQL

的大叔覺得這種說法也有點兒道理，他們竟然同意在一些情況下把非分組列也放到查詢清單中，這就設計到一個稱之為

sql_mode

的系統變量，我們先看一下在我的電腦上這個系統變量的值：

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)           

哇唔，好長的一段。不過大家不必在意，我們隻關心其中一個稱之為

ONLY_FULL_GROUP_BY

的家夥。隻要

sql_mode

的值裡邊有這個東東，

MySQL

伺服器就“比較正常”（也就是不允許非分組列放到查詢清單中），但是如果我們把這個東東從

sql_mode

系統變量中移除（移除這個東東隻要重新設定一下這個系統變量，把這個東東從值裡邊去除掉就好，我們現在不必要關心值裡邊兒後邊那一坨東西是幹嘛的，照着抄下來就好）：

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)           

然後再執行上邊那個曾經報錯的語句：

mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject;
+-----------------------------+-----------+------------+
| subject                     | name      | AVG(score) |
+-----------------------------+-----------+------------+
| 母豬的産後護理              | 杜子騰    |    73.0000 |
| 論薩達姆的戰争準備          | 杜子騰    |    73.2500 |
+-----------------------------+-----------+------------+
2 rows in set (0.00 sec)           

看，這回就不會報錯了。但這是個好事兒麼？個人覺得不是，因為

MySQL

伺服器也不能保證結果集中的

name

列的值到底是分組中的哪條記錄的。大家在日常工作中，也希望盡量不要用這個投機取巧的功能，沒啥卵用，而且容易産生錯誤。

小貼士： 不同MySQL版本中sql_mode的值可能預設包含ONLY_FULL_GROUP_BY這個家夥，也可能不包含ONLY_FULL_GROUP_BY這個家夥，也就是說不同MySQL版本中可能預設不支援查詢清單中包含非分組列，也可能預設支援查詢清單中包含非分組列。

來源：微信公衆号

作者：小孩子4919

原文：MySQL：為什麼查詢清單中多了它，GROUP BY語句就會報錯呢？