本文介绍如何使用 mysql 8.0+ 的递归 cte 功能，从零生成指定月份（如三月）完整连续的 31 天日期列表，即使数据库中仅存部分日期记录。
本文介绍如何使用 mysql 8.0+ 的递归 cte 功能，从零生成指定月份（如三月）完整连续的 31 天日期列表，即使数据库中仅存部分日期记录。
在实际业务开发中，常遇到“数据稀疏但需完整展示”的场景：例如数据库中仅存储了 3 月 1 日、3 日、9 日和 14 日的销售记录，但报表要求横轴必须覆盖 3 月 1–31 日全量日期，缺失日显示为 0 或 NULL。此时不能依赖现有表数据，而需主动构造日期序列。
MySQL 8.0 引入了标准 SQL 的递归公用表表达式（Recursive CTE），是生成连续日期最简洁、高效且无需临时表或存储过程的方案。
✅ 核心实现：递归 CTE 构造三月日期
以下 SQL 可生成 2022 年 3 月 1 日至 31 日共 31 条日期记录：
WITH RECURSIVE march_days AS (
SELECT DATE('2022-03-01') AS d
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY)
FROM march_days
WHERE d < '2022-03-31'
)
SELECT d AS date_of_march FROM march_days;? 说明：
- 初始成员（anchor）设定起始日 DATE('2022-03-01')；
- 递归成员每次加 1 天，终止条件为 d
- 最终结果按自然顺序返回 31 行 DATE 类型值。
? 关联真实业务数据（推荐用法）
单纯生成日期意义有限，通常需与业务表左连接以补全空值。假设存在销售表 sales，含字段 sale_date 和 amount：
WITH RECURSIVE march_days AS (
SELECT DATE('2022-03-01') AS d
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY)
FROM march_days
WHERE d < '2022-03-31'
)
SELECT
m.d AS date,
COALESCE(s.amount, 0) AS daily_sales
FROM march_days m
LEFT JOIN sales s ON m.d = s.sale_date
ORDER BY m.d;该查询将确保输出严格 31 行，缺失销售记录的日期 daily_sales 显示为 0。
⚠️ 注意事项与最佳实践
- 版本限制：递归 CTE 仅支持 MySQL 8.0 及以上版本；若使用 MariaDB，请确认版本 ≥ 10.2.2 且启用 cte 插件。
- 性能提示：默认递归深度上限为 1000（可通过 SET cte_max_recursion_depth = 1000; 调整），31 天完全无压力。
- 月份通用化：如需适配任意月份，可将 '2022-03-01' 和 '2022-03-31' 替换为动态计算（例如 LAST_DAY() + DAYOFMONTH() 组合），但需注意月末天数差异（28/29/30/31）。
- 时区安全：所有日期字面量建议使用 DATE() 显式转换，避免隐式类型转换引发意外。
✅ 总结
利用递归 CTE 生成指定月份完整日期序列，是 MySQL 高级查询的典型应用。它逻辑清晰、执行高效，且天然支持与业务表 LEFT JOIN 实现“稀疏数据 → 密集展示”的关键转换。掌握此技巧，可显著提升报表类、时间维度分析类 SQL 的健壮性与可维护性。
