報錯信息

DataGrip 報錯還原

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

Navicat 報錯還原

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

報錯原因

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

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

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

# SELECT 查詢的字段和 GROUP BY 分組依據(jù)的字段不統(tǒng)一，會報錯。
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)一后就不會報錯。
SELECT class_number, class_name FROM student GROUP BY class_number, class_name ;

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

解決方案

查看當(dāng)前 sql mode

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

SELECT @@global.sql_mode;

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

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

方案一：臨時解決

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

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ù)庫重啟后，還是回歸默認(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'

注意：

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

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

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

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

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

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

實際使用效果如下：

• group_concat()：
  • 將分到同一組的數(shù)據(jù)默認(rèn)用逗號隔開作為返回數(shù)據(jù)。

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

實際使用效果如下：

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

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

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

最新評論