MySQL only_full_group_by 1055 报错的三种解决方案，临时关闭有影响吗？

2021-09-19 00:43:52

本文首发：MySQL only_full_group_by 1055报错的三种解决方案，临时关闭有影响吗？
当我们迁移到 MySQL 5.7+ 的版本时，常会碰到 ERROR 1055 only_full_group_by 错误，这是 5.7 之后 SQL_MODE 默认打开了严格模式导致的错误。说明你代码里有地方写的不严谨。

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'kalacloud.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible 
with sql_mode=only_full_group_by

我看到大多数教程，只写了解决这个问题「术」的部分，并没有讲解什么原因导致这个错误。本教程先从原理讲起，先让大家理解为什么会出错。然后给出三种解决方案：「彻底解决」、「临时解决」和「折中解决」，你可根据自己的实际情况进行选择。

SQL_MODE 是什么？

讲 ONLY_FULL_GROUP_BY 错误前，我们先来说一下 SQL_MODE。理解 MySQL 工作原理能更好的帮你理解错误发生的本质原因。
SQL_MODE 是 MySQL 中的一个环境变量，定义了 MySQL 支持的 SQL 语法和数据校验程度。
SQL_MODE 一共三种模式

ANSI 模式：宽松模式。对插入数据进行校验，如不符合定义类型或长度，对保存数据进行截断。
TRADITIONAL 模式：严格模式。对插入数据进行严格校验，保证错误数据不能插入，ERROR 报错。用于事物时，事物会进行回滚。
STRICT_TRANS_TABLES 模式：严格模式。对插入数据进行严格校验，错误数据不能插入，ERROR 报错。

MySQL 5.7.4 之前，MySQL 默认不开启严格模式

这是 MySQL 升级到5.7.5 之后默认SQL_MODE 为严格模式：

扩展阅读：MySQL 连接数过多的处理方法合集 - Too many connections

SQL_MODE 严格模式的意义

在宽松模式下，即便 insert 一个错误的数据，MySQL 也会不加判断的全盘接受。
我们来看一个实例
我们首先关闭 SQL_MODE 严格模式

set session sql_mode='';

然后我们创建一个表并向其中插入一组超范围的数据

create table kalacloud_t1(website char(9));
insert into kalacloud_t1 values('kalacloud.com');

返回值：

从返回值可以看出，我们向 websie cher(9) 中插入了一条长为 13 的值，没有报错，直接插入，但超过 9 的部分，即「.com」被截断丢掉了。
接着我们在严格模式下试试，首先打开 SQL_MODE 严格模式：

set session sql_mode='TRADITIONAL';

create table kalacloud_t2(website char(9));
insert into kalacloud_t2 values('kalacloud.com');

返回值：

我们可以从返回值看出，MySQL 直接报错，告诉你插入的数据有问题。
扩展阅读：MySQL 配置文件 my.cnf / my.ini 逐行详解

ONLY_FULL_GROUP_BY 问题及解决方案

接着我们来说说 ONLY_FULL_GROUP_BY 的问题。当我们数据库迁移至 5.7 或者 8.0 之后，最常见的错误就是 Error 1055 only_full_group_by 错误。
正如我前文所写，这个错误的关键原因是不规范的 SQL 语法，5.7 之后默认 SQL_MODE 变为严格模式。
我们来一起看一个实例，这是一组卡拉云用户点击网页的 log 记录：

现在我们使用 GROUP BY 来排序找出访问量最大的网页。
我们先关掉 sql_mode 的严格模式来试试：

在宽松模式下，我们可以看出这个 query 虽然可以查询，但语法和逻辑上稍有问题。我们想对 page_url 进行排序，但 query 中也加入了 user_id ，在返回值中可以发现问题，index.html 这个页面不仅 user_id 1 的用户访问过，用户 2 和 3 也访问了，那么这张返回的表表格数据就是有问题的。
user_id 1 列在返回数据里，到底代表什么？是第一个访问 index.html 还是最后一个访问这个页面的意思呢？没人知道，这是个随机盲盒，运行原理未知。
我们打开 sql_mode 严格模式跑一下上面这段代码：

返回一个 ERROR 1055 报错。
ONLY_FULL_GROUP_BY 是 SQL_MODE 中TRADITIONAL 的选项参数，从 5.7 开始默认开启为严格模式。这就是为什么大家迁移到 MySQL 新版会报 1055 错误的原因。
我们已经理解了这个问题的原理原因，接下来，碰到 ONLY_FULL_GROUP_BY 报错，我们应该怎么处理呢？
扩展阅读：如何在 MySQL 中查询数据库中带有某个字段的所有表名

解决方案 1 – 重写代码

找到报错语法中错误的部分，根据逻辑重写 query，本示例中，我们去掉 user_id 即可。

如果你有大量在旧版 SQL 完成的代码，检查这种错误很有可能是相当浩大的工程。这时就到了你的决断时刻了，你有两条路可选：
（1）干脆推翻重写，让代码保持严谨和整洁，养成良好的习惯，避免未来出现的未知错误。
（2）了解原理。知道可能会出现的问题，关掉严格模式，回到宽松模式，接着用旧版代码。

解决方案 2 – 返回宽松模式

如果你碰到 ERROR 1055 报错，但也不想在浩如烟海的代码中纠错，那么你可以回到「宽松模式」接着用。
在 MySQL 5.7 及以上版本中 SQL_MODE 包含

ONLY_FULL_GROUP_BY、STRINCT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER

我们直接在 MySQL 配置文件中更改，或者临时全部关闭：

SET GLOBAL sql_mode='';

或者单关闭 ONLY_FULL_GROUP_BY

MariaDB [kalacloud_demo]> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

解决方案 3 – 使用聚合函数

如果某些特别的原因就是要查询 user_id ，但你又没空改代码，那么我可使用聚合函数来规避这类错误，算是一种折中的方案了，语法改严谨了，代码又不需要大动。
我们可以使用MAX()、MIN()或者GROUP_CONCAT() 聚合函数。

MySQL 还提供了 ANY_VALUE() 函数，来解决这类问题：

扩展阅读：MySQL 分组查询实战如何查询每组第一、最后、单多字段查询、分组筛选、最大小值排序

总结和推荐

在代码严谨、清晰的道路上走多远都不为过，我个人推荐使用第一种解决方案，趁着这一次机会，把你代码中潜藏的隐患一网打尽。之后遵循严谨的代码逻辑来写代码。当然，有很多特殊情况，你也可以参照方案二、三来临时处理。
接着推荐一下我开发的卡拉云，只要你会写 MySQL ，就能使用卡拉云搭建自己的数据工具，比如，数据看板，企业 CRM、ERP，权限管理后台，对账系统等。
卡拉云是新一代低代码开发工具，免安装部署，可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流，定制开发。无需繁琐的前端开发，只需要简单拖拽，即可快速搭建企业内部工具。数月的开发工作量，使用卡拉云后可缩减至数天。

卡拉云可一键接入常见的数据库及 API
卡拉云可根据公司工作流需求，轻松搭建数据看板或其他内部工具，并且可一键分享给组内的小伙伴。

下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台，仅需要简单拖拽即可快速生成前端组件，只要会写 SQL，便可搭建一套趁手的数据库工具。立即使用卡拉云。

有关 MySQL 教程，可继续拓展学习：

码农公寓