柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) MySQL報(bào)錯(cuò)：sql

http://yzkb.51969.com/

Linux環(huán)境

ubuntu 22.04 MySQL是8.0.35版本

問(wèn)題描述

Expression?#1?of?SELECT?list?is?not?in?GROUP?BY?clause?and?contains?nonaggregated?column?'auth_system.t_class_temp_config.id'?which?is?not?functionally?dependent?on?columns?in?GROUP?BY?clause;?this?is?incompatible?with?sql_mode=only_full_group_by

問(wèn)題分析

這個(gè)錯(cuò)誤是由于 MySQL 數(shù)據(jù)庫(kù)的 SQL 模式中啟用了 only_full_group_by，它要求在使用 GROUP BY 語(yǔ)句時(shí)，SELECT 中的字段必須要么包含在 GROUP BY 子句中，要么是聚合函數(shù)的參數(shù)。在查詢中，SELECT 中的字段不符合這個(gè)規(guī)則，導(dǎo)致了錯(cuò)誤的出現(xiàn)。

因?yàn)樵贛ySQL 5.7后，MySQL默認(rèn)開(kāi)啟了SQL_MODE嚴(yán)格模式，對(duì)數(shù)據(jù)進(jìn)行嚴(yán)格校驗(yàn)。如果代碼中含有g(shù)roup by聚合操作，那么select中的列，除了使用聚合函數(shù)之外的，如max()、min()等，都必須出現(xiàn)在group by中。

舉例說(shuō)明：

錯(cuò)誤示范：

select Chengdu,Shanghai from city group by Chengdu

正確示范：

select Chengdu,Shanghai from city group by Chengdu,Shanghai

解決方法

方案一：更改數(shù)據(jù)庫(kù)配置

1.在Linux環(huán)境先登錄MySQL

2.在MySQL中輸入如下命令進(jìn)行查看

select @@global.sql_mode;

可以發(fā)現(xiàn)MySQL的sql_mode是開(kāi)啟了ONLY_FULL_GROUP_NY。

說(shuō)明：解釋sql_mode的含義

（1）ONLY_FULL_GROUP_NY

??????? 含義：?jiǎn)⒂眠@個(gè)模式后，MySQL 在執(zhí)行 GROUP BY 操作時(shí)，要求 SELECT 語(yǔ)句中的列必須是 GROUP BY 子句中的分組列或者包含在聚合函數(shù)中。換句話說(shuō)，要求 SELECT 中的非聚合列必須在 GROUP BY 子句中出現(xiàn)。

????????作用：這個(gè)模式的作用是增強(qiáng) SQL 查詢語(yǔ)句的嚴(yán)格性，避免因數(shù)據(jù)未正確分組而導(dǎo)致結(jié)果不確定性的情況發(fā)生。通過(guò)強(qiáng)制要求所有非聚合列都在 GROUP BY 子句中列出，確保了查詢結(jié)果的準(zhǔn)確性和一致性

（2）STRICT_TRANS_TABLES

????????含義：當(dāng)啟用這個(gè)模式時(shí)，MySQL 會(huì)對(duì)事務(wù)中的數(shù)據(jù)插入和更新進(jìn)行嚴(yán)格的類型檢查，確保數(shù)據(jù)的準(zhǔn)確性和完整性。

????????作用：在插入或更新數(shù)據(jù)時(shí)，如果發(fā)生類型不匹配或者值超出范圍等問(wèn)題，將會(huì)產(chǎn)生錯(cuò)誤，確保了數(shù)據(jù)的一致性。

（3）NO_ZERO_IN_DATE

????????含義：禁止在日期中使用 '0000-00-00' 或 '0000-00-00 00:00:00' 這樣的值。

????????作用：當(dāng)啟用這個(gè)模式時(shí)，MySQL 會(huì)禁止插入或更新日期字段為零值，以避免不合法的日期值的存儲(chǔ)。

（4）NO_ZERO_DATE

????????含義：禁止在日期中使用 '0000-00-00' 這樣的值。

????????作用：與 NO_ZERO_IN_DATE 類似，這個(gè)模式也是為了避免不合法的日期值的存儲(chǔ)。

（5）ERROR_FOR_DIVISION_BY_ZERO

?????????含義：當(dāng)啟用這個(gè)模式時(shí)，MySQL 在發(fā)生除零錯(cuò)誤時(shí)將產(chǎn)生一個(gè)錯(cuò)誤，而不是返回 NULL 或者一個(gè)警告。

?????????作用：這個(gè)模式確保了在執(zhí)行除法操作時(shí)，避免了由于除零而導(dǎo)致的意外結(jié)果，提高了數(shù)據(jù)計(jì)算的準(zhǔn)確性。

（6）NO_ENGINE_SUBSTITUTION

?????????含義：如果指定的存儲(chǔ)引擎不可用，將不會(huì)自動(dòng)替換為默認(rèn)的存儲(chǔ)引擎。

?????????作用：這個(gè)模式要求所指定的存儲(chǔ)引擎必須可用，如果不可用，則會(huì)產(chǎn)生錯(cuò)誤，確保了使用正確的存儲(chǔ)引擎。

3.重新設(shè)置sql_mode

即把ONLY_FULL_GROUP_NY.去除，其他不變即可。

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

這個(gè)方案有個(gè)缺點(diǎn)就是重啟數(shù)據(jù)庫(kù)之后，配置會(huì)失效。

方案二：更改數(shù)據(jù)庫(kù)配置（永久生效）

找到MySQL安裝時(shí)的配置文件my.cnf，在mysqld下添加如下配置即可。

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

保存并退出。重啟MySQL即可生效。

驗(yàn)證

?方案三：調(diào)整sql語(yǔ)句

根據(jù)您的實(shí)際需求，可以修改查詢語(yǔ)句，確保 SELECT 中的字段要么包含在 GROUP BY 子句中，要么作為聚合函數(shù)的參數(shù)。例如：

SELECT id, MAX(column_name) FROM your_table GROUP BY id;

方案四:臨時(shí)表

在子查詢中使用臨時(shí)表，以避免直接在主查詢中使用 GROUP BY。這種方法可能需要對(duì)查詢進(jìn)行重新設(shè)計(jì)，但可以繞過(guò) only_full_group_by 的限制。

參考：MySQL報(bào)錯(cuò)：sql_mode=only_full_group_by 4種解決方法含舉例，輕松解決ONLY_FULL_GROUP_BY的報(bào)錯(cuò)問(wèn)題-CSDN博客

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) MySQL報(bào)錯(cuò)：sql

http://yzkb.51969.com/

推薦文章