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深入思考

我们在工作中会频繁的使用到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进行分组，然后按照品牌名称就筛选出了一共五组品牌，然后再从各自品牌中选出最低价格便可得到最终结果。

后记总结

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