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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 当我们迁移到 MySQL 5.7+ 的版本时,常会碰到 `ERROR 1055 only_full_group_by` 错误,这是 5.7 之后 `SQL_MODE` 默认打开了严格模式导致的错误。说明你代码里有地方写的不严谨。

MySQL only_full_group_by 1055报错的三种解决方案,临时关闭有影响吗?
本文首发: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.4 之前
这是 MySQL 升级到5.7.5 之后默认SQL_MODE 为严格模式:
MySQL 升级到5.7.5 之后
扩展阅读: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');

返回值:
关闭 SQL MODE 严格模式
从返回值可以看出,我们向 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');

返回值:
开启 SQL MODE 严格模式
我们可以从返回值看出,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 记录:
卡拉云用户点击网页的 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_BYSQL_MODETRADITIONAL 的选项参数,从 5.7 开始默认开启为严格模式。这就是为什么大家迁移到 MySQL 新版会报 1055 错误的原因。
我们已经理解了这个问题的原理原因,接下来,碰到 ONLY_FULL_GROUP_BY 报错,我们应该怎么处理呢?
扩展阅读:如何在 MySQL 中查询数据库中带有某个字段的所有表名

解决方案 1 – 重写代码

找到报错语法中错误的部分,根据逻辑重写 query,本示例中,我们去掉 user_id 即可。
ONLY_FULL_GROUP_BY 重写代码
如果你有大量在旧版 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() 聚合函数。
ONLY_FULL_GROUP_BY 使用聚合函数
MySQL 还提供了 ANY_VALUE() 函数,来解决这类问题:
ONLY_FULL_GROUP_BY 使用 any value
扩展阅读:MySQL 分组查询实战 如何查询每组第一、最后、单多字段查询、分组筛选、最大小值排序

总结和推荐

在代码严谨、清晰的道路上走多远都不为过,我个人推荐使用第一种解决方案,趁着这一次机会,把你代码中潜藏的隐患一网打尽。之后遵循严谨的代码逻辑来写代码。当然,有很多特殊情况,你也可以参照方案二、三来临时处理。
接着推荐一下我开发的卡拉云,只要你会写 MySQL ,就能使用卡拉云搭建自己的数据工具,比如,数据看板,企业 CRM、ERP,权限管理后台,对账系统等。
卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天。
卡拉云可一键接入常见的数据库及 API
卡拉云可一键接入常见的数据库及 API
卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。
使用卡拉云轻松搭建企业内部工具
下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。立即使用卡拉云
使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台
有关 MySQL 教程,可继续拓展学习:

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
163 3
Mysql高可用架构方案
|
1月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
1天前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
9天前
|
关系型数据库 MySQL Linux
升级到MySQL 8.4,MySQL启动报错:io_setup() failed with EAGAIN
当MySQL 8.4启动时报错“io_setup() failed with EAGAIN”时,通常是由于系统AIO资源不足所致。通过增加AIO上下文数量、调整MySQL配置、优化系统资源或升级内核版本,可以有效解决这一问题。上述解决方案详细且实用,能够帮助管理员快速定位并处理此类问题,确保数据库系统的正常运行。
48 9
|
10天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
1月前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
131 3
|
1月前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
43 1
|
1月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
73 5
|
1月前
|
NoSQL 安全 关系型数据库
2024Mysql And Redis基础与进阶操作系列(6)作者——LJS[含MySQL 多表之一对一/多;多对多;多表联合查询等详解步骤及常见报错问题所对应的解决方法]
MySQL 多表之一对一/多;多对多;多表联合之交叉连接;内连接;左、右、外、满、连接;子查询及关键字;自连接查询等详解步骤及常见报错问题所对应的解决方法
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法