递归生成序列
递归 CTE 可以生成连续整数或日期序列，用来补全报表里空缺的日期：先给出起点，再用 UNION ALL 递归出下一行，直到满足终止条件。PostgreSQL 另有 generate_series 这类集合返回函数，写法更短；MySQL 没有等价函数，只能用递归 CTE 或持久化的日历表。
基本信息
| MySQL 写法 | WITH RECURSIVE … UNION ALL |
|---|---|
| MySQL 限制 | 受 cte_max_recursion_depth 限制（默认 1000 层） |
| PostgreSQL 写法 | generate_series 支持数字与时间步长 |
| 常见用途 | 与业务表左连接补全缺失日期 |
| 注意 | 序列与业务表按日期连接时，时区与截断口径要一致 |
典型使用场景
- 报表补齐没有数据的日期，避免折线断点
- 生成连续编号给批量任务做分片
- 构造测试数据行
- 按小时或按天展开时间范围做统计
- 替代应用层循环插入日历数据
MySQL 写法
MySQL 写法
-- 生成 1 到 100 的连续整数
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 100 -- 终止条件写在递归分支里
)
SELECT n FROM seq;
-- 生成日期序列
WITH RECURSIVE days AS (
SELECT DATE '2024-01-01' AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < DATE '2024-01-31'
)
SELECT d FROM days;PostgreSQL 写法
PostgreSQL 写法
-- 生成 1 到 100
SELECT n FROM generate_series(1, 100) AS n;
-- 生成日期序列
SELECT d::date
FROM generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day') AS d;
-- 与业务表左连接，补全没有数据的日期
SELECT d::date AS day, COALESCE(SUM(o.total), 0) AS amount
FROM generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day') AS d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + INTERVAL '1 day'
GROUP BY d ORDER BY d;两库对照
| 维度 | MySQL 8 | PostgreSQL |
|---|---|---|
| 递归写法 | WITH RECURSIVE | WITH RECURSIVE |
| 内置序列函数 | 无 | generate_series |
| 深度限制 | cte_max_recursion_depth 默认 1000 | 无同类参数，但大序列同样吃内存 |
| 日期步长 | 用 INTERVAL 相加 | generate_series 直接支持步长 |
| 超大数据量 | 建议改成持久化日历表 | 同样建议持久化 |
递归 CTE 每一轮都要产生新行，生成上万行会明显变慢：高频使用的日历建议落地成持久化表，一次生成长期复用。
序列与业务表按日期连接时要统一截断口径：created_at 带时分秒，直接与日期相等比较会漏掉当天其余数据，要用半开区间或先转换成日期。
批量任务分片可以借这个序列：把任务 ID 按序列切成若干段并行执行，注意每段边界统一为左闭右开，避免某一行被两个任务同时处理。
生成序列后先核对行数与端点：起始、终止或步长写错时语句往往仍能正常执行，但区间差一天就会让整张报表对不上。