MySQL报错：sql_mode=only_full_group_by解决方法

Linux环境

ubuntu 22.04 MySQL是8.0.35版本

问题描述

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

问题分析

这个错误是由于 MySQL 数据库的 SQL 模式中启用了 only_full_group_by，它要求在使用 GROUP BY 语句时，SELECT 中的字段必须要么包含在 GROUP BY 子句中，要么是聚合函数的参数。在查询中，SELECT 中的字段不符合这个规则，导致了错误的出现。

因为在MySQL 5.7后，MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，除了使用聚合函数之外的，如max()、min()等，都必须出现在group by中。

举例说明：

错误示范：

select Chengdu,Shanghai from city group by Chengdu

正确示范：

select Chengdu,Shanghai from city group by Chengdu,Shanghai

解决方法

方案一：更改数据库配置

1.在Linux环境先登录MySQL

2.在MySQL中输入如下命令进行查看

select @@global.sql_mode;

可以发现MySQL的sql_mode是开启了ONLY_FULL_GROUP_NY。

说明：解释sql_mode的含义

（1）ONLY_FULL_GROUP_NY

        含义：启用这个模式后，MySQL 在执行 GROUP BY 操作时，要求 SELECT 语句中的列必须是 GROUP BY 子句中的分组列或者包含在聚合函数中。换句话说，要求 SELECT 中的非聚合列必须在 GROUP BY 子句中出现。

        作用：这个模式的作用是增强 SQL 查询语句的严格性，避免因数据未正确分组而导致结果不确定性的情况发生。通过强制要求所有非聚合列都在 GROUP BY 子句中列出，确保了查询结果的准确性和一致性

（2）STRICT_TRANS_TABLES

        含义：当启用这个模式时，MySQL 会对事务中的数据插入和更新进行严格的类型检查，确保数据的准确性和完整性。

        作用：在插入或更新数据时，如果发生类型不匹配或者值超出范围等问题，将会产生错误，确保了数据的一致性。

（3）NO_ZERO_IN_DATE

        含义：禁止在日期中使用 '0000-00-00' 或 '0000-00-00 00:00:00' 这样的值。

        作用：当启用这个模式时，MySQL 会禁止插入或更新日期字段为零值，以避免不合法的日期值的存储。

（4）NO_ZERO_DATE

        含义：禁止在日期中使用 '0000-00-00' 这样的值。

        作用：与 NO_ZERO_IN_DATE 类似，这个模式也是为了避免不合法的日期值的存储。

（5）ERROR_FOR_DIVISION_BY_ZERO

         含义：当启用这个模式时，MySQL 在发生除零错误时将产生一个错误，而不是返回 NULL 或者一个警告。

         作用：这个模式确保了在执行除法操作时，避免了由于除零而导致的意外结果，提高了数据计算的准确性。

（6）NO_ENGINE_SUBSTITUTION

         含义：如果指定的存储引擎不可用，将不会自动替换为默认的存储引擎。

         作用：这个模式要求所指定的存储引擎必须可用，如果不可用，则会产生错误，确保了使用正确的存储引擎。

3.重新设置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';

这个方案有个缺点就是重启数据库之后，配置会失效。

方案二：更改数据库配置（永久生效）

找到MySQL安装时的配置文件my.cnf，在mysqld下添加如下配置即可。

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

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

验证

 方案三：调整sql语句

根据您的实际需求，可以修改查询语句，确保 SELECT 中的字段要么包含在 GROUP BY 子句中，要么作为聚合函数的参数。例如：

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

方案四:临时表

在子查询中使用临时表，以避免直接在主查询中使用 GROUP BY。这种方法可能需要对查询进行重新设计，但可以绕过 only_full_group_by 的限制。

参考：MySQL报错：sql_mode=only_full_group_by 4种解决方法含举例，轻松解决ONLY_FULL_GROUP_BY的报错问题-CSDN博客