only_full_group_by问题而引发的对group by的深入思考

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: only_full_group_by问题而引发的对group by的深入思考

问题背景

最近在项目中使用mysql的group by进行分组查询的场景比较多,其中一次遇到了一个问题,即在开发环境执行一个如下sql时是正确且可执行的,

select a,b,max(c) from test_tbl group by a

但是放到了测试环境就会报如下的错误。

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

解决办法

因为开发环境和测试环境所使用的mysql数据库的版本不一样,开发环境使用的时候5.6.x而测试环境使用的是5.7.x,而在Mysql的5.7.x版本中默认是开启sql_mode = only_full_group_by

而在这个模式下,我们使用分组查询时,出现在select字段后面的只能是group by后面的分组字段,或使用聚合函数包裹着的字段。

在上面的sql中,字段b既不是group by里面的分组字段,也没有被聚合函数包裹着。5.7以下的版本不进行检查,而5.7以上的版本进行了sql_mode=only_full_group_by的检查,所以会出现以上的问题,当然解决方法也很简单,将b也纳入到分组字段中即可。

group by深入思考

虽然在工作中会频繁的使用到group by进行分组查询,但自己对数据分组这个概念一直很模糊,这次就借着这个机会,通过一个简单的示例来帮助大家在脑海中建立起来数据分组这个抽象概念。

我们创建一张如下的数据表

CREATE TABLE `product` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `product_name` varchar(100) NOT NULL COMMENT '商品名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `category_name` varchar(100) NOT NULL COMMENT '商品分类',
  `price` decimal(10,4) NOT NULL COMMENT '价格'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试用的商品表';

表中有如下数据

mysql> select * from product;
+----+---------------+------------+---------------+-------+
| id | product_name  | brand_name | category_name | price |
+----+---------------+------------+---------------+-------+
|  1 | XiaoMi6X      | XiaoMi     | 手机          | 2999  |
|  2 | XiaoAi        | Lenovo     | 电脑          | 8999  |
|  3 | HUAWEI K1     | HUAWEI     | 手机          | 1999  |
|  4 | iPhone X      | iPhone     | 手机          | 9999  |
|  5 | MacBook Pro   | Mac        | 电脑          | 13999 |
|  6 | iPhone XMAX   | iPhone     | 手机          | 10999 |
|  7 | HUAWEI Mate20 | HUAWEI     | 手机          | 2999  |
+----+---------------+------------+---------------+-------+
7 rows in set

一个最高价

我们使用group by按照品牌类目(category_name)进行分组,并获取该分组中的最高价格。

mysql> select category_name,max(price) from product group by category_name;
+---------------+------------+
| category_name | max(price) |
+---------------+------------+
| 手机          | 10999      |
| 电脑          | 13999      |
+---------------+------------+
2 rows in set

这个简单的分组sql相信也难不倒大家,那么让我们来看看这个分组查询语句是如何取到最终结果的。

过程:如下图所示,首先从数据集中筛选出来类目为手机的所有记录(以蓝色线框标明)和类目为电脑的所有记录(以红色线框标明)并将这些记录归集到一起,那么分组之后就出现了以不同类目进行划分的两个数据集,然后再从各自的数据集中选出最高的价格便可得到最终的结果。

一个最低价

再举一个例子,我们用品牌名称brand_name进行分组,并使用聚合函数算出该品牌下的最低价格。

mysql> select brand_name,min(price) from product group by brand_name;
+------------+------------+
| brand_name | min(price) |
+------------+------------+
| HUAWEI     | 1999       |
| iPhone     | 9999       |
| Lenovo     | 8999       |
| Mac        | 13999      |
| XiaoMi     | 2999       |
+------------+------------+
5 rows in set

过程:如下图所示,从数据集中按照品牌名称brand_name进行分组,然后按照品牌名称就筛选出了一共五组品牌,然后再从各自品牌中选出最低价格便可得到最终结果。

后记总结

取经归来后,愈发的觉得无论生活还是技术都应该持有一种脱离表层、向往深层的探索追求精神,在这个过程中我会不断的总结分享,与诸君共勉! 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8月前
|
开发框架 .NET 编译器
C#-Group By 的使用
group by 是linq中的分组功能,能通过给定的字段对数据集进行分组,得到分组后的结果。
160 0
|
10月前
|
关系型数据库 MySQL Windows
Mysql (ONLY_FULL_GROUP_BY) Expression #1 of SELECT list is not in GROUP BY ...
Mysql (ONLY_FULL_GROUP_BY) Expression #1 of SELECT list is not in GROUP BY ...
71 0
|
12月前
|
SQL 关系型数据库 MySQL
GROUP BY和ORDER BY的区别
GROUP BY和ORDER BY的区别
243 0
group by+group_concat解决的小问题
group by+group_concat解决的小问题
80 0
|
SQL
ORDER BY && GROUP BY
ORDER BY && GROUP BY
69 0
ORDER BY && GROUP BY
group by
group by
71 0
|
SQL
group实例
group实例
81 0
|
SQL
十、GROUP BY 和 HAVING 的使用
十、GROUP BY 和 HAVING 的使用
251 0
|
SQL 关系型数据库 数据挖掘
你真的懂使用Group by?
你真的懂使用Group by?
477 0
你真的懂使用Group by?