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

DataGrip 報(bào)錯(cuò)還原

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx庫(kù).xx表.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Navicat 報(bào)錯(cuò)還原

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘school.student_100w.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

報(bào)錯(cuò)原因

MySQL 5.7后，MySQL的 SQL_MODE 配置項(xiàng)會(huì)默認(rèn)包含 only_full_group_by 模式，這個(gè)配置會(huì)嚴(yán)格執(zhí)行SQL92標(biāo)準(zhǔn)。如果代碼中含有g(shù)roup by聚合操作，那么select中的列，除了使用聚合函數(shù)之外的，如max()、min()等，都必須出現(xiàn)在group by中。

要求：從學(xué)生表中，根據(jù)班級(jí)編號(hào)，分組查詢班級(jí)編號(hào)、班級(jí)名稱。

報(bào)錯(cuò)：SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段不統(tǒng)一。

# SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段不統(tǒng)一，會(huì)報(bào)錯(cuò)。
SELECT class_number, class_name FROM student GROUP BY class_number;

調(diào)整：將SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一。

# SELECT 查詢字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一后就不會(huì)報(bào)錯(cuò)。
SELECT class_number, class_name FROM student GROUP BY class_number, class_name ;

# SELECT 查詢字段和 GROUP BY 分組依據(jù)的字段統(tǒng)一后就不會(huì)報(bào)錯(cuò)。
SELECT class_number FROM student GROUP BY class_number;

解決方案

查看當(dāng)前 sql mode

執(zhí)行 SQL 語(yǔ)句，查看當(dāng)前的 sql_mode 。

SELECT @@global.sql_mode;

注意默認(rèn)的列寬可能無(wú)法直接查看全部?jī)?nèi)容，容易造成誤解，需要手動(dòng)拉伸列寬查看。

拉伸列寬后可查看全部?jī)?nèi)容，（不同版本的 MySQL 默認(rèn)值可能不同）。

方案一：臨時(shí)解決

排除當(dāng)前 sql_mode 中 ONLY_FULL_GROUP_BY 值，復(fù)制剩下的值。執(zhí)行設(shè)置語(yǔ)句：

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

但是如果當(dāng)前數(shù)據(jù)庫(kù)重啟后，還是回歸默認(rèn)設(shè)置，即包含 ONLY_FULL_GROUP_BY 模式。

方案二：永久解決

windows系統(tǒng)，直接修改 my.ini 配置文件。

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

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

注意：

• 不能有多余的符號(hào)，我之前在結(jié)尾加了分號(hào)導(dǎo)致重啟失敗。
• 點(diǎn)擊保存文件，不要直接關(guān)掉。 不然設(shè)置丟失，是不會(huì)生效的。

Linux系統(tǒng)，思路是一樣的，都是找配置文件修改配置。但是不同Mysql版本，路徑和配置文件名稱可能不同：大致可能是 /etc/my.cnf 或者 /mysql.conf 之類的名稱。

數(shù)據(jù)庫(kù)重啟后生效。

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

• any_value()：
  • 將分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回?cái)?shù)據(jù)。 （any_value()函數(shù)就是MySQL提供的用來(lái)抑制ONLY_FULL_GROUP_BY值被拒絕的）

SELECT class_number, any_value(class_name) FROM student GROUP BY class_number;

實(shí)際使用效果如下：

• group_concat()：
  • 將分到同一組的數(shù)據(jù)默認(rèn)用逗號(hào)隔開(kāi)作為返回?cái)?shù)據(jù)。

SELECT class_number, group_concat(class_name) FROM student GROUP BY class_number;

實(shí)際使用效果如下：

方案四：調(diào)整實(shí)現(xiàn)思路，避開(kāi) GROUP BY 使用

通過(guò)對(duì)SQL的查詢思路調(diào)整，代碼邏輯調(diào)整等，部分場(chǎng)景是可以繞開(kāi)對(duì) GROUP BY 關(guān)鍵字篩選依賴的。這個(gè)需要結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景，自己去思考調(diào)整了。

到此這篇關(guān)于MySQL報(bào)錯(cuò)sql_mode=only_full_group_by的問(wèn)題解決的文章就介紹到這了,更多相關(guān)MySQL sql_mode=only_full_group_by內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論