MySQL使用技巧整理

---

递归函数进行日期补全

递归函数WITH RECURSIVE的语法（在MySQL8.0版本以上支持）

WITH RECURSIVE cte_name AS (
    -- 基础查询（非递归部分，初始化数据）
    SELECT ...
    UNION [ALL]
    -- 递归查询（引用 cte_name，生成后续数据）
    SELECT ...
    FROM cte_name
    WHERE ...
)
-- 主查询，使用 CTE 结果
SELECT ... FROM cte_name ...;

• 基础查询（Base Case）：
  递归的起点，生成初始结果集。通常是一个简单的 SELECT 语句，不涉及对 cte_name 的引用。

• 递归查询（Recursive Case）：
  通过 UNION 或 UNION ALL 与基础查询连接。引用 cte_name 自身，基于前一次的结果生成新的数据行。通常包含 WHERE 条件，控制递归终止。

• UNION 或 UNION ALL：
  连接基础查询和递归查询的结果。
  NION ALL 更常见，因为它允许重复行且性能更好（UNION 会去重，增加开销）。

• 主查询：
  在 CTE 定义后，使用 SELECT … FROM cte_name 引用递归 CTE 的结果进行进一步处理。

1. 示例1

# 统计一年中每个月的数据，如果该月没有数据则显示0
with recursive months as (select 1 as month
                          union all
                          select month + 1
                          from months
                          where month < 12)
select months.month,COALESCE(t2.cnt,0) as count
from months
         left join
     (select count(1)         as cnt,
      month(alarmtime) as month
      from forest_fireproof.alarm_info
      where alarmtime between '2025-01-01' and '2025-12-31'
      group by month(alarmtime)) t2 on months.month = t2.month;

2. 示例2

# 从当前月份往前推11个月，统计历史数据趋势，没有数据则显示0
WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') AS month_date
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(month_date, INTERVAL 1 MONTH), '%Y-%m-01')
    FROM months
    WHERE month_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
)
        SELECT
            DATE_FORMAT(m.month_date, '%Y-%m') AS month,
            IFNULL(COUNT(e.id), 0) AS event_count
        FROM months m
        LEFT JOIN zjg_event_info e ON DATE_FORMAT(e.create_time, '%Y-%m') = DATE_FORMAT(m.month_date, '%Y-%m')
            AND (e.event_type = '3' or (e.event_type = '2' and e.scope_flag = '1'))
        GROUP BY month
        ORDER BY month;