MySQL 8 其他新特性
MySQL 8 其他新特性
1. MySQL 8 新特性概述
1.1 MySQL 8.0 新增特性
- 窗口函数：支持
ROW_NUMBER()、RANK()、DENSE_RANK()等函数，可对分区数据进行排序、排名和分析。 - 公用表表达式（CTE）：支持普通CTE和递归CTE，简化复杂查询的编写。
1.2 MySQL 8.0 移除的旧特性
- 部分过时的SQL模式、系统变量和语法被移除，以优化性能和规范性。
2. 新特性1：窗口函数
2.1 使用窗口函数前后对比
窗口函数与分组聚合不同，它不会将结果合并为一条记录，而是将聚合结果附加到每一条原始记录中，便于进行分区内的排名、前后对比等操作。
2.2 窗口函数分类
- 静态窗口函数：窗口大小固定（如序号函数、分布函数）。
- 动态窗口函数：窗口大小随记录变化（如前后函数、首尾函数）。
2.3 语法结构
窗口函数名() OVER ([PARTITION BY 分区列] [ORDER BY 排序列]
) AS 别名
2.4 分类讲解
1. 序号函数
| 函数 | 说明 |
|---|---|
ROW_NUMBER() |
顺序排序，生成连续唯一序号 |
RANK() |
并列排序，相同值序号相同，后续序号不连续（如1,1,3） |
DENSE_RANK() |
并列排序，相同值序号相同，后续序号连续（如1,1,2） |
示例：按类别对商品价格降序排序
-- ROW_NUMBER()
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stock
FROM goods;-- RANK()
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stock
FROM goods;-- DENSE_RANK()
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stock
FROM goods;-- 取每个类别价格最高的3种商品
SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, category_id, category, NAME, price, stockFROM goods
) t WHERE row_num <= 3;
2. 分布函数
| 函数 | 说明 |
|---|---|
PERCENT_RANK() |
等级百分比，公式：(rank - 1) / (rows - 1) |
CUME_DIST() |
累积分布值，查询小于或等于当前值的比例 |
示例：计算商品价格的百分比排名和累积分布
-- PERCENT_RANK()
SELECT RANK() OVER w AS r,PERCENT_RANK() OVER w AS pr,id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);-- CUME_DIST()
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,id, category, NAME, price
FROM goods;
3. 前后函数
| 函数 | 说明 |
|---|---|
LAG(expr, n) |
返回当前行前n行的expr值 |
LEAD(expr, n) |
返回当前行后n行的expr值 |
示例：查询商品价格与前/后一个商品的差值
-- LAG()：前一个商品价格差值
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (SELECT id, category, NAME, price,LAG(price, 1) OVER w AS pre_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;-- LEAD()：后一个商品价格差值
SELECT id, category, NAME, behind_price, price, behind_price - price AS diff_price
FROM (SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_priceFROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)
) t;
4. 首尾函数
| 函数 | 说明 |
|---|---|
FIRST_VALUE(expr) |
返回分区内第一个expr的值 |
LAST_VALUE(expr) |
返回分区内最后一个expr的值 |
示例：按价格排序，查询每个类别第一个和最后一个商品的价格
-- FIRST_VALUE()
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER(PARTITION BY category_id ORDER BY price) AS first_price
FROM goods;-- LAST_VALUE()
SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);
5. 其他函数
| 函数 | 说明 |
|---|---|
NTH_VALUE(expr, n) |
返回分区内第n个expr的值 |
NTILE(n) |
将分区数据分为n个桶，记录桶编号 |
示例：
-- NTH_VALUE()：查询排名第2和第3的价格
SELECT id, category, NAME, price,NTH_VALUE(price, 2) OVER w AS second_price,NTH_VALUE(price, 3) OVER w AS third_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);-- NTILE()：按价格将商品分为3组
SELECT NTILE(3) OVER w AS nt, id, category, NAME, price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price);