0


Mysql ONLY_FULL_GROUP_BY模式详解、group by非查询字段报错

文章目录

以下内容基于Mysql8.0进行讲解

ONLY_FULL_GROUP_BY

模式。

一、问题报错

Mysql5.7版本以上对group by 分组有了新需求,要求group by 后的字段要与select后查询的字段一致,否则就会报错,报错信息如下:

[2024-09-29 10:48:54] [42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tbl_test.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

测试用例如下:

createtable tbl_test(
    id intprimarykeyauto_increment,
    name varchar(30)CHARACTERSET utf8mb4 COLLATE utf8mb4_unicode_ci uniquenotnull,
    age intcomment'年龄',
    address varchar(50)comment'住址',
    update_time datetimedefaultnull)comment'测试表';insertinto tbl_test(name,age,address,update_time)values('zhangsan',25,'杭州',now()),('李四',21,'武汉',now()),('王五',25,'杭州',now());

在这里插入图片描述
对上述数据同时查询name与age字段,并根据age字段group by 后报错如下:
在这里插入图片描述
原因分析 :MySQL5.7版本及以上默认设置了 mysql sql_mode =

only_full_group_by

属性,导致报错。

其中ONLY_FULL_GROUP_BY就是造成这个错误的罪魁祸首了,在这种严格模式下,对于group by聚合操作,若在select中的列没有在group by中出现,那么这个SQL就是不合法的。因为开发写的sql中,select列不在group by从句中,在使用group by时就会报错。

接下来我们一起看一下这个模式的原理以及这种情况如何解决。

二、ONLY_FULL_GROUP_BY模式

2.1、什么是ONLY_FULL_GROUP_BY?

ONLY_FULL_GROUP_BY

是MySQL的一个SQL模式(SQL_MODE)之一,它要求在使用

GROUP BY

语句时,

SELECT

列表、

HAVING

条件或

ORDER BY

列表中的每个列,要么是聚合函数的一部分(如

COUNT()

,

SUM()

,

AVG()

等),要么必须在

GROUP BY

子句中明确指定。

这一模式的设计初衷是增强查询的准确性和可预测性,避免因为列的不明确引用而导致的数据错误或不一致。

2.2、为什么要使用ONLY_FULL_GROUP_BY?

  • 数据准确性:确保聚合查询的结果符合预期,防止因为非聚合列的不确定行为而导致的数据误导。
  • 一致性:在不同的数据库系统或配置间保持查询行为的一致性,减少迁移或升级时的兼容性问题。
  • 避免歧义:清晰定义查询的意图,减少因查询理解错误而导致的错误。

2.3、查看sql_mode

SELECT @@sql_mode;
或者
select @@GLOBAL.sql_mode;

查询出来的值为:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

以下为sql_mode常用值的含义,参考:

ONLY_FULL_GROUP_BY:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零
NO_ZERO_DATE:设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

三、解决方法

对于上述示例中的报错,有以下两种思路解决问题。

  1. 关闭only_full_group_by模式(若没有历史数据的情况下不建议关闭该模式)
  2. 官方说明了:You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column. 您可以在不禁 ONLY_FULL_GROUP_BY用 的情况下通过ANY_VALUE()引用非聚合列来实现相同的效果。

3.1、关闭only_full_group_by模式

3.1.1、方法一:关闭当前会话中的only_full_group_by

在客户端工具中依次输入以下语句

# 查询当前的sql模式
select @@global.sql_mode;

# 去除掉only_full_group_by后再设置到系统中
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

在这里插入图片描述
注意:这种方法只在当前回话中有效,重启mysql后会失效

再次执行本文开头示例中的sql就不会报错了
在这里插入图片描述

3.1.2、方法二:永久关闭only_full_group_by模式

(1) 找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)

(2) 找到当前配置的sql_mode那行,去掉ONLY_FULL_GROUP_BY;

如果没有,就在文件内的[mysqld]后增加配置:

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

(3)保存配置文件后,重启Mysql。

如果重启后也不会生效,检查下sql_mode的位置是不是不对(放在最后是不会生效的):
在这里插入图片描述

3.2、使用ANY_VALUE()函数

ANY_VALUE():将分到同一组的数据里第一条数据的指定列值作为返回数据。

SELECT any_value(name),age  FROM  tbl_test groupby age;

在这里插入图片描述

四、其他

4.1、无权限报错

本人执行

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

报错了:

[Err]1227- Access denied; you need (at least one of) the SUPER privilege(s)for this operation

说明当前用户没有权限;需要联系管理员执行才行;

改服务器配置也同理,如果没有权限,联系管理员。

4.2、select后面的字段必须在group by后面出现?当group by遇上唯一索引或主键

我们上面讲了ONLY_FULL_GROUP_BY模式下select中的列必须在group by中出现,但是经过测试后发现 若group by 后面分组字段是唯一索引或者是主键,那么select后面可以跟其他的列

可能看了这句话不太明白,那么我们用示例演示下, 以下示例基于

ONLY_FULL_GROUP_BY

模式下:

createtable tbl_test(
    id intprimarykeyauto_increment,-- 注意: name是唯一索引字段
    name varchar(30)CHARACTERSET utf8mb4 COLLATE utf8mb4_unicode_ci uniquenotnull,
    age intcomment'年龄',
    address varchar(50)comment'住址',
    update_time datetimedefaultnull)comment'测试表';insertinto tbl_test(name,age,address,update_time)values('zhangsan',25,'杭州',now()),('李四',21,'武汉',now()),('王五',25,'上海',now());

在这里插入图片描述

对上述数据同时查询name与age字段,并根据age字段group by 后报错如下:
在这里插入图片描述
因为开启了ONLY_FULL_GROUP_BY模式,分组字段是age但是查询字段里面包含了name,所以报错了。

select后面的字段必须在group by后面出现吗?测试中偶然发现事实并非如此,看下图示例:
在这里插入图片描述
可以看到上述示例中,group by后面根据name进行分组,select后面跟了age、address字段但是也没报错。

这是为什么呢?难道group by后面分组字段的值不重复就能跟其他字段么?示例中address字段也是不重复的,那么我们用address进行分组试下,结果报错如下:
在这里插入图片描述

经多次测试后发现: 若group by后面分组字段是唯一索引或主键字段,那么select 后面可以跟其他字段。

官方文档中也证实了此结论。
官网地址:https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
官方解释是:当group by 后面跟上主键或者不为空唯一索引时,查询是有效的,因为此时的每一笔数据都具有唯一性。
在这里插入图片描述


本文转载自: https://blog.csdn.net/weixin_49114503/article/details/142630502
版权归原作者 五月天的尾巴 所有, 如有侵权,请联系我们删除。

“Mysql ONLY_FULL_GROUP_BY模式详解、group by非查询字段报错”的评论:

还没有评论