mysql5-7与mysql8-0关于with-rollup-order-by处理上的区别

---

title: mysql5.7与mysql8.0关于with rollup order by处理上的区别
date: 2019-10-23 16:39:09

tags:

需求：

常规问题，group by之后根据指定字段进行排序，如下根据c字段排序。
eg：select a,b,count(*) from tb_1 group by a,b with rollup order by c;

问题：

Q1：5.7就不支持with rollup 与 order by(distinct)一起用，这么优秀的功能，为啥不支持呢？

因为内部数据排序实在聚合计算之前，最后聚合列是直接被加在最后面的。并且5.7group by默认是排序的。如果从一开始就已经拍好顺序了，那后面也就不会再排序或者去重，那自然也就无法支持自定义order by或者distinct操作了。

Q2：8.0.12开始支持，那8.0.12为啥能支持呢？以及最后出现的GROUPING函数是干啥的？

待研究，目测是SQL的执行顺序变了吧
https://mysqlserverteam.com/mysql-8-0-grouping-function/

Q3：用一个例子说明支持后的使用效果吧

表结构如下：

root@test05:05:10>select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
+------+------+------+

5.7支持group by字段指定排序，而8.0是不支持的

5.7
root@test05:11:22>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ASC , b DESC WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   12 |   12 |
|  111 |   11 |   11 |
|  111 | NULL |   23 |
|  222 |   23 |   23 |
|  222 |   22 |   22 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.00 sec)

8.0
root@test05:13:09>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ASC , b DESC WITH ROLLUP;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC , b DESC WITH ROLLUP' at line 1

5.7不支持order by，而8.0开始支持order by

5.7
root@test05:13:04>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a,b;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

8.0
root@test05:13:13>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a,b;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
| NULL | NULL |   68 |
|  111 | NULL |   23 |
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  222 | NULL |   45 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
+------+------+------+
7 rows in set (0.00 sec)

8.0支持order by任意列

5.7
root@test05:18:33>SELECT a, b, c, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY c desc;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

8.0
root@test05:18:38>SELECT a, b, c, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY c;
+------+------+------+------+
| a    | b    | c    | SUM  |
+------+------+------+------+
|  111 |   11 |   11 |   11 |
|  111 |   12 |   12 |   12 |
|  111 | NULL |   12 |   23 |
|  222 |   22 |   22 |   22 |
|  222 |   23 |   23 |   23 |
|  222 | NULL |   23 |   45 |
| NULL | NULL |   23 |   68 |
+------+------+------+------+

8.0的GROUPING函数可以实现和5.7同样的效果

5.7
root@test05:20:01>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.00 sec)

8.0
root@test05:19:38>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ,b WITH ROLLUP ORDER BY GROUPING(a), a, GROUPING(b), b;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.00 sec)

参考文档：
https://mysqlserverteam.com/improvements-to-rollup-in-mysql/