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

HiJiangChuan · · 145 次点击 · 开始浏览    置顶

![MySQL only_full_group_by 1055报错的三种解决方案,临时关闭有影响吗?](https://kalacloud.com/static/1b4fb1029d1aa6e3f54d1df51614556f/ef245/head.jpg) 本文首发:[MySQL only_full_group_by 1055报错的三种解决方案,临时关闭有影响吗?](https://kalacloud.com/blog/solve-query-failures-regarding-only-full-group-by-sql-mode/) 当我们迁移到 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 之前](https://kalacloud.com/static/d614d78b4ce792038dc4c3fa99eaf6c1/8cdda/01-mysql574.png) 这是 MySQL 升级到5.7.5 之后默认SQL_MODE 为严格模式: ![MySQL 升级到5.7.5 之后](https://kalacloud.com/static/3bf660d905475a5a391aa37cff0f3473/efb68/02-mysql575.png) 扩展阅读:[MySQL 连接数过多的处理方法合集 - Too many connections](https://kalacloud.com/blog/mysql-error-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 严格模式](https://kalacloud.com/static/ee0b16eea19bc886a8ad478074a7f30c/5f652/03-sql_mode-traditional-off.png) 从返回值可以看出,我们向 `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 严格模式](https://kalacloud.com/static/92ca9549b535f64316ef3b23344a5469/eee07/04-sql_mode-traditional-on.png) 我们可以从返回值看出,MySQL 直接报错,告诉你插入的数据有问题。 扩展阅读:[MySQL 配置文件 my.cnf / my.ini 逐行详解](https://kalacloud.com/blog/how-to-edit-mysql-configuration-file-my-cnf-ini/) ## ONLY_FULL_GROUP_BY 问题及解决方案 接着我们来说说 `ONLY_FULL_GROUP_BY` 的问题。当我们数据库迁移至 5.7 或者 8.0 之后,最常见的错误就是 `Error 1055 only_full_group_by` 错误。 正如我前文所写,这个错误的关键原因是不规范的 SQL 语法,5.7 之后默认 `SQL_MODE` 变为严格模式。 我们来一起看一个实例,这是一组[卡拉云](https://kalacloud.com/)用户点击网页的 log 记录: ![卡拉云用户点击网页的 log 记录](https://kalacloud.com/static/c1b3a13d70239c300101bb37853f587a/56e36/05-kalacloud-web-log.png) 现在我们使用 `GROUP BY` 来排序找出访问量最大的网页。 我们先关掉 `sql_mode` 的严格模式来试试: ![关闭严格模式](https://kalacloud.com/static/98c691e03ffcf5c294526daaa6545ed1/dd507/06-kalacloud-web-log-off.png) 在宽松模式下,我们可以看出这个 query 虽然可以查询,但语法和逻辑上稍有问题。我们想对 `page_url` 进行排序,但 query 中也加入了 `user_id` ,在返回值中可以发现问题,index.html 这个页面不仅 user_id 1 的用户访问过,用户 2 和 3 也访问了,那么这张返回的表表格数据就是有问题的。 user_id 1 列在返回数据里,到底代表什么?是第一个访问 index.html 还是最后一个访问这个页面的意思呢?没人知道,这是个随机盲盒,运行原理未知。 我们打开 `sql_mode` 严格模式跑一下上面这段代码: ![打开严格模式](https://kalacloud.com/static/017666f5894e7549e1dbf7c32a1594f3/29beb/07-kalacloud-web-log-on.png) 返回一个 ERROR 1055 报错。 `ONLY_FULL_GROUP_BY` 是 `SQL_MODE` 中`TRADITIONAL` 的选项参数,从 5.7 开始默认开启为严格模式。这就是为什么大家迁移到 MySQL 新版会报 1055 错误的原因。 我们已经理解了这个问题的原理原因,接下来,碰到 `ONLY_FULL_GROUP_BY` 报错,我们应该怎么处理呢? 扩展阅读:[如何在 MySQL 中查询数据库中带有某个字段的所有表名](https://kalacloud.com/blog/find-all-tables-with-specific-column-names-in-mysql/) ## 解决方案 1 – 重写代码 找到报错语法中错误的部分,根据逻辑重写 query,本示例中,我们去掉 `user_id` 即可。 ![ONLY_FULL_GROUP_BY 重写代码](https://kalacloud.com/static/6ff045feefb9d3889fe8c481b1c60742/5dded/08-kalacloud-web-log-recode.png) 如果你有大量在旧版 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 使用聚合函数](https://kalacloud.com/static/cc255ff8524bb599bf8e4ad16e0973fc/29beb/09-kalacloud-web-log-max-min.png) MySQL 还提供了 `ANY_VALUE()` 函数,来解决这类问题: ![ONLY_FULL_GROUP_BY 使用 any value](https://kalacloud.com/static/8db35c3501876a25bfae91649086d00c/61016/10-kalacloud-web-log-any-value.png) 扩展阅读:[MySQL 分组查询实战 如何查询每组第一、最后、单多字段查询、分组筛选、最大小值排序](https://kalacloud.com/blog/how-to-group-by-find-first-and-last-record-in-mysql/) ## 总结和推荐 在代码严谨、清晰的道路上走多远都不为过,我个人推荐使用第一种解决方案,趁着这一次机会,把你代码中潜藏的隐患一网打尽。之后遵循严谨的代码逻辑来写代码。当然,有很多特殊情况,你也可以参照方案二、三来临时处理。 接着推荐一下我开发的[卡拉云](https://kalacloud.com/),只要你会写 MySQL ,就能使用卡拉云搭建自己的数据工具,比如,数据看板,企业 CRM、ERP,权限管理后台,对账系统等。 卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。**数月的开发工作量,使用卡拉云后可缩减至数天。** ![卡拉云可一键接入常见的数据库及 API](https://kalacloud.com/static/18822b2a23183deb7d11dd484a7f65aa/71c1d/97-kalacloud-sql.png) 卡拉云可一键接入常见的数据库及 API 卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。 ![使用卡拉云轻松搭建企业内部工具](https://kalacloud.com/5400a60956e16d655e0297c5d6e5a8d2/98-kalacloud-gif.gif) 下图为使用卡拉云在 5 分钟内搭建的「[优惠券发放核销](https://kalacloud.com/blog/coupon-system-design/)」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。**立即使用[卡拉云](https://kalacloud.com/)。** ![使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台](https://kalacloud.com/static/34625d3adaea4ed250ff3f05b863e47c/cca35/99-kalacloud-sql-index.png) 有关 MySQL 教程,可继续拓展学习: - [如何远程连接 MySQL 数据库,阿里云腾讯云外网连接教程](https://kalacloud.com/blog/how-to-allow-remote-access-to-mysql/) - [MySQL 时间戳用什么类型 - MySQL 时间函数详解](https://kalacloud.com/blog/difference-between-mysql-datetime-and-timestamp-datatypes/) - [MySQL 中如何实现 BLOB 数据类型的存取,BLOB 有哪些应用场景?](https://kalacloud.com/blog/how-to-use-the-mysql-blob-data-type-to-store-images-with-php-or-kalacloud/) - [如何在 MySQL / MariaDB 中跳过多张表导出或指定多张表导出备份](https://kalacloud.com/blog/how-to-dump-database-and-ignore-or-specific-some-tables-with-mysqldump-in-mysql/) - [如何将 MySQL / MariaDB 的查询结果保存到文件](https://kalacloud.com/blog/how-to-save-mysql-mariadb-query-output-to-a-file/) - [MySQL 分组查询实战 如何查询每组最第一条、最后一条记录,单多字段查询、分组筛选、最大小值排序](https://kalacloud.com/blog/how-to-group-by-find-first-and-last-record-in-mysql/)

有疑问加站长微信联系(非本文作者)

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:701969077

145 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传