MySQL报错：sql_mode=only_full_group_by解决方法_Mysql

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博客

MySQL报错：sql_mode=only_full_group_by解决方法

2024年08月06日 • Mysql •我要评论