mysql5.7版本以上引起的group by报错ONLY_FULL_GROUP_BY

ens
311 阅读4分钟

一、问题描述

对于如下sql

select a,b,count(c) from t_xxx group by a;

在mysql5.7以前的版本是可以运行的，但是在5.7以后的版本这条语句会报错。

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、原因

1. 版本问题：

纠其原因，是由于mysql5.7+默认开启了​​ONLY_FULL_GROUP_BY​​​模式，该模式要求​​select​​​的语句中的列必须在​​group by​​​中存在，也就是说，如果你把上诉​​sql​​改成如下就不会报错

select a,b,count(c) from t_xxx group by a,b;

那么为什么mysql5.7+要开启这个模式呢？

如果你用过oracle的话你就会知道，oracle中，就是和开启了这个参数的情况是一致的，oracle要求要求​​select​​​的语句中的列必须在​​group by​​中存在，其实开启这个参数是对的，不开启反而会有数据截断的可能。

所以建议写sql的时候就规范写，即​​select​​的语句中的列必须在​​group by​​中存在

但在某些情况下我们需要关闭这个参数，比如我们项目里的很多sql都是已经写好且他们在mysql5.7以下的版本中运行的很正常，这个时候我们升级了mysql的版本，那么如果去修改项目里的sql未免有点太闹腾。此时关闭这个参数就是最好的解决方案，那么如何关闭呢？

2. SQL书写规范问题：

因为 MySQL 默认设置了 “sql_mode=only_full_group_by”，即 “仅全分组模式”，查询时，被 select 的每一列都必须满足下述条件之一：① 该列为被聚合列（即被应用聚合函数的列）或存在于 group by 从句中；② 该列必须 功能上依赖于 group by 指定的列。

注：“功能上依赖于”，参考 MySQL 页面，其解释是：“如果列 x 无重复值，且其中每个值都可以唯一确定列 y 中的值，那么列 y 在功能上依赖于列 x”。具体情况是，如果列 x 是 primary key，或者 x 是一个无重复值（unique）且非空（not null）的索引（index），那么其他列会在功能上依赖于列 x。

三、解决方案

1. 修改sql书写规范

(1) 当出错列可由 group by 的列唯一确定，那么对出错列使用 any_value() 即可，其功能相当于一个聚合函数。 (2) 如果出错列不可由 group by 的列唯一确定，那么查询本身就是有歧义的。 (3) 如果不在意选出 (2) 中描述的不确定的结果，可以关闭 only_full_group_by 模式，操作如下

2. 修改 sql_mode

我们先查询当前的​​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​​这个项给删掉，那么如何删掉呢？

临时解决方案

执行如下​​sql​​

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

但上诉更改只最新建的库有效，对于已经存在的数据库，应该执行如下

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

永久解决方案

但这两个都是临时的解决方案，只对当前会话有效，如果你需要永久生效则需要修改​​mysql​​的配置文件，该文件在windows系统下叫​​my.ini​​，而在linux系统下叫​​my.conf​​，它们一般存在于mysql的安装根目录下

image.png

需要注意的是部分版本的​​mysql​​可能没有这个文件，此时新建就好,

你需要在该文件的[mysqld]下追加如下

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

最终的my.ini文件配置如下所示

[mysqld]
## 省略其他配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysql]

注意：​​sql_mode​​的值最好为你前面通过select查询出来的值去掉了ONLY_FULL_GROUP_BY之后的值，如果你查出来的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则为

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SU

参考文章：
mysql5.7版本以上引起的group by报错ONLY_FULL_GROUP_BY）
【MySQL】only_full_group_by 模式导致的报错 — 解释与解决

avatar
文章
阅读
粉丝