mysql5.7版本以上引起的group by报错ONLY_FULL_GROUP_BY（mysql5.7+ 关闭ONLY_FULL_GROUP_BY）

快来打我* 2022-12-27 11:12 120阅读 0赞

# 问题描述 #

对于如下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

# 原因 #

纠其原因，是由于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未免有点太闹腾。此时关闭这个参数就是最好的解决方案，那么如何关闭呢？

# 解决方案 #

我们先查询当前的`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的安装根目录下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMzI5MzM0MzI_size_16_color_FFFFFF_t_70]  
需要注意的是部分版本的`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_SUBSTITUTION

# 参考 #

https://www.jb51.net/article/172172.htm  
https://blog.csdn.net/oarsman/article/details/77742799  
https://www.cnblogs.com/zenghi-home/p/13397649.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMzI5MzM0MzI_size_16_color_FFFFFF_t_70]: /images/20221120/001a50ba986e477c9de09926619f0856.png