MySQL

MySQL 8.4.3 引入 GROUP BY ROLLUP 语法

2026-01-25 1.4k words 6 min read views

本期内容以最新版本 MySQL 8.4.8 为例，介绍 8.4 系列的新特性。

MySQL 8.4.3 中新增替代语法

MySQL 8.4.3 引入了与 Oracle、SQL Server、PostgreSQL 等数据库兼容的 ROLLUP 语法形式：

-- 传统语法（仍支持）
SELECT year, country, SUM(profit) 
FROM sales 
GROUP BY year, country WITH ROLLUP;

-- 8.4.3 新增的替代语法（结果完全相同）
SELECT year, country, SUM(profit) 
FROM sales 
GROUP BY ROLLUP (year, country);

测试用例：

-- 1. 创建测试表
CREATE TABLE sales (
    year INT,
    country VARCHAR(50),
    product VARCHAR(50),
    profit INT
);

-- 2. 插入测试数据
INSERT INTO sales (year, country, product, profit) VALUES
(2023, 'USA', 'Laptop', 1500),
(2023, 'USA', 'Phone', 800),
(2023, 'China', 'Laptop', 2000),
(2023, 'China', 'Phone', 1200),
(2024, 'USA', 'Laptop', 1700),
(2024, 'USA', 'Phone', 900),
(2024, 'China', 'Laptop', 2200),
(2024, 'China', 'Phone', 1300);

测试结果：

mysql> select version()\G
*************************** 1. row ***************************
version(): 8.4.8
1 row in set (0.00 sec)

mysql> SELECT year, country, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country WITH ROLLUP;
+------+---------+-------------+
| year | country | SUM(profit) |
+------+---------+-------------+
| 2023 | China   |        3200 |
| 2023 | USA     |        2300 |
| 2023 | NULL    |        5500 |
| 2024 | China   |        3500 |
| 2024 | USA     |        2600 |
| 2024 | NULL    |        6100 |
| NULL | NULL    |       11600 |
+------+---------+-------------+
7 rows in set (0.00 sec)

mysql> SELECT year, country, SUM(profit)
    -> FROM sales
    -> GROUP BY ROLLUP (year, country);
+------+---------+-------------+
| year | country | SUM(profit) |
+------+---------+-------------+
| 2023 | China   |        3200 |
| 2023 | USA     |        2300 |
| 2023 | NULL    |        5500 |
| 2024 | China   |        3500 |
| 2024 | USA     |        2600 |
| 2024 | NULL    |        6100 |
| NULL | NULL    |       11600 |
+------+---------+-------------+
7 rows in set (0.00 sec)

结果包含以下三个层级的聚合：

明细层级：每个国家、每年的利润总和。
小计层级：每年的全球总利润（此时 country 列显示为 NULL）。
总计层级：所有年份、所有国家的总利润（此时 year 和 country 均显示为 NULL）。

02. WITH ROLLUP 的适用场景

无论使用哪种语法，ROLLUP 的核心价值在于单次查询生成多级聚合报表：

1. 财务报表与 OLAP 分析

生成分层小计（Subtotal）和总计（Grand Total），无需 UNION ALL：

mysql> SELECT
    ->     COALESCE(region, 'Grand Total') AS region,
    ->     COALESCE(department, 'Subtotal') AS dept,
    ->     SUM(revenue) AS revenue
    -> FROM sales
    -> GROUP BY ROLLUP (region, department);
+-------------+-------------+---------+
| region      | dept        | revenue |
+-------------+-------------+---------+
| East        | Appliances  | 1800.00 |
| East        | Electronics | 1500.00 |
| East        | Subtotal    | 3300.00 |
| North       | Appliances  | 1200.00 |
| North       | Electronics | 2000.00 |
| North       | Subtotal    | 3200.00 |
| South       | Beauty      | 2000.00 |
| South       | Subtotal    | 2000.00 |
| Grand Total | Subtotal    | 8500.00 |
+-------------+-------------+---------+
9 rows in set, 1 warning (0.00 sec)

2. 数据仓库 ETL 与报表生成

配合 GROUPING() 函数（返回 1 表示 ROLLUP 生成的聚合行，0 表示原始数据行），可标记汇总行类型：

mysql> INSERT INTO sales (year, country, product, profit) VALUES
    -> (2023, 'USA', 'Laptop', 1500.00),
    -> (2023, 'USA', 'Phone', 800.00),
    -> (2023, 'UK', 'Laptop', 1200.00),
    -> (2024, 'USA', 'Laptop', 1700.00),
    -> (2024, 'UK', 'Phone', 900.00),
    -> (2024, NULL, 'Unknown', 500.00); -- 这条数据用于验证 GROUPING 函数
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->     IF(GROUPING(year), 'All Years', year) AS year,
    ->     IF(GROUPING(country), 'All Countries', country) AS country,
    ->     SUM(profit) AS profit
    -> FROM sales
    -> GROUP BY ROLLUP (year, country);
+-----------+---------------+---------+
| year      | country       | profit  |
+-----------+---------------+---------+
| 2023      | UK            | 1200.00 |
| 2023      | USA           | 2300.00 |
| 2023      | All Countries | 3500.00 |
| 2024      | NULL          |  500.00 | -- 结果集中看到一个 NULL 行代表未知国家
| 2024      | UK            |  900.00 |
| 2024      | USA           | 1700.00 |
| 2024      | All Countries | 3100.00 |
| All Years | All Countries | 6600.00 |
+-----------+---------------+---------+
8 rows in set, 2 warnings (0.00 sec)

3. 多级维度分析

支持任意层级的维度组合，例如按年、季、月分析销售趋势：

mysql> SELECT
    ->     IF(GROUPING(yr_val), 'All Years', yr_val) AS yr,
    ->     IF(GROUPING(qtr_val), 'All Quarters', qtr_val) AS qtr,
    ->     IF(GROUPING(mo_val), 'All Months', mo_val) AS mo,
    ->     SUM(amount) AS total
    -> FROM (
    ->     SELECT YEAR(order_date) AS yr_val,
    ->            QUARTER(order_date) AS qtr_val,
    ->            MONTH(order_date) AS mo_val,
    ->            amount
    ->     FROM orders
    -> ) AS t
    -> GROUP BY ROLLUP (yr_val, qtr_val, mo_val);
+-----------+--------------+------------+----------+
| yr        | qtr          | mo         | total    |
+-----------+--------------+------------+----------+
| 2024      | 1            | 1          |  1500.00 |
| 2024      | 1            | 2          |  1200.00 |
| 2024      | 1            | All Months |  2700.00 |
| 2024      | 2            | 4          |  2000.00 |
| 2024      | 2            | 6          |  1500.00 |
| 2024      | 2            | All Months |  3500.00 |
| 2024      | 3            | 9          |   800.00 |
| 2024      | 3            | All Months |   800.00 |
| 2024      | 4            | 12         |  3000.00 |
| 2024      | 4            | All Months |  3000.00 |
| 2024      | All Quarters | All Months | 10000.00 |
| All Years | All Quarters | All Months | 10000.00 |
+-----------+--------------+------------+----------+
12 rows in set (0.00 sec)

03. MySQL 8.4.3 版本的相关修复

除语法增强外，8.4.3 及后续版本还修复了多个 WITH ROLLUP 相关的稳定性问题：

聚合函数与 ROLLUP 的交互：修复了包含聚合函数（如 COUNT()）且同时出现在 ORDER BY 中的 ROLLUP 查询可能导致服务器异常退出的问题
子查询兼容性：修复了子查询中包含 WITH ROLLUP 时处理不正确的问题
窗口函数结合：修复了 RANK()...OVER() 与 ROLLUP 结合时的断言失败问题

04. 使用建议

新项目：建议使用 8.4.3+ 的新语法 GROUP BY ROLLUP (...)，更易读且符合 SQL 标准
旧项目迁移：传统 WITH ROLLUP 语法仍完全支持，无需强制修改
复杂报表：结合 GROUPING() 函数和 COALESCE()/IF() 对 NULL 值进行标签化处理，提升报表可读性

Have a nice day ~ ☕

🌻 近期内容 ▼

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）、AI、Vibe Coding、OpenClaw 、Hermes Agent 等感兴趣，可以加群一起聊聊。关注微信公众号：(少安事务所)，后台回复[群]，即可看到入口。如果这篇文章为你带来了灵感或启发，请帮忙『点赞、推荐、转发』吧，感谢！ღ( ´･ᴗ･` )~

oracle mysql

Author: Shawn Yan

Link: https://shawnyan.cn/2026/mysql/mysql-8-4-3-introduce-group-by-rollup/index.html

License: All articles on this site are original unless otherwise stated. Please indicate the source when reprinting!