「MySQL 基础」使用MySQL8生成指定月份日期列表&年份日期列表&季度日期列表&周日期列表业务需求引入： 在业务中

🙏废话不多说系列，直接开整🙏

---

• 业务需求引入：
• • 在业务中经常会遇到 统计当前月份的数据指标，而从数据库业务表中查询出来的只会是指定日期的业务数据，所以如果需要数据库 SQL 一并返回一张完整的查询数据，我们可以借鉴如下方式处理：
• 总体实现思路：
• • 虚拟一张日期表；（本文主要将如何根据 MySQL 8 来实现一张日期表）
  • 日期表 关联 业务数据表 最终得到一张完整统计数据；
  • 注意事项：如果你的 MySQL 实例有大量的并发查询或者这个查询在大型数据集上运行，递归 CTE 可能会影响性能。在这种情况下，你可能需要考虑其他方法，比如使用应用程序代码来生成日期列表，或者将日期列表存储在一个单独的表中。
• 根据上述实现的思路，我们可以得到如下：
  1. 虚拟一张 当日所在 月 的所有日期枚举表；
  2. 虚拟一张 当日所在 周 的所有日期枚举表；
  3. 虚拟一张 当日所在 年 的所有日期枚举表；
  4. 虚拟一张 当日所在 季度 的所有日期枚举表；

（1）MySQL8 得到当【月】所有天数日期

-- 得到一张之还有当前日期所在月份（指定日期：2024-02）的虚拟表 DateSeries 
WITH RECURSIVE DateSeries AS (
    SELECT "2024-02-01" AS date_value  
    UNION ALL  
    SELECT DATE_ADD(date_value, INTERVAL 1 DAY)  
    FROM DateSeries  
    WHERE MONTH(date_value) = MONTH(DATE_ADD(date_value, INTERVAL 1 DAY))  
      AND YEAR(date_value) = YEAR(DATE_ADD(date_value, INTERVAL 1 DAY))  
)  
SELECT date_value  
FROM DateSeries  
WHERE YEAR(date_value) = YEAR("2024-02-01")  
  AND MONTH(date_value) = MONTH("2024-02-01")  
ORDER BY date_value;

测试结果如下：

扩展：如果我们传入的日期为  “2024-02-28” , 那么我们会得到什么样的日期集合呢？答案是：只会有 【2024-02-28, 2024-02-29】这两个日期的哦 😄。

（2）MySQL8 得到指定【周】的所有日期

【1】方式一：临时变量 & 3个临时表

SET @start_date = DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);  
SET @end_date = DATE_ADD(@start_date, INTERVAL 6 DAY);  
SELECT DATE_ADD(@start_date, INTERVAL a.a + (10 * b.a) + (100 * c.a) DAY) AS week_date  
FROM (  
    SELECT 0 AS a  UNION ALL SELECT 1  
    UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4  
    UNION ALL SELECT 5  UNION ALL SELECT 6  UNION ALL SELECT 7  
) AS a  
CROSS JOIN (  
    SELECT 0 AS a  
    UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3  
    UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6 
    UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9  
) AS b  
CROSS JOIN (  
    SELECT 0 AS a  
) AS c  
WHERE DATE_ADD(@start_date, INTERVAL a.a + (10 * b.a) + (100 * c.a) DAY) <= @end_date  
ORDER BY week_date;

演示结果：

SQL 逻辑分析：

1. 这个查询首先计算出当前日期所在周的起始日期（@start_date）和结束日期（@end_date） 。然后，它使用三个交叉连接（CROSS JOIN）的子查询来生成一个数字序列，该序列用于添加到起始日期上以生成一周内的所有日期。
2. 注意，我们使用了三个子查询的交叉连接来生成一个足够大的数字序列，以覆盖一周内的所有日期。在这个例子中，我们只使用了一个子查询 c，因为我们知道一周只有7天，所以不需要更多的数字。但是，这个结构可以很容易地扩展到更大的日期范围。
3. 最后，我们使用 WHERE 子句来限制生成的日期在起始日期和结束日期之间，并使用 ORDER BY 子句对结果进行排序。

【2】方式二：临时变量 & 1个临时表

SET @current_date = CURDATE();  -- 此处可以修改为指定日期，例如 改为 SET @current_date = "2024-01-01";
SET @start_of_week = DATE_SUB(@current_date, INTERVAL WEEKDAY(@current_date) DAY);  
SET @end_of_week = DATE_ADD(@start_of_week, INTERVAL 6 DAY);  
  
SELECT  
    DATE_ADD(@start_of_week, INTERVAL a.daynum DAY) AS week_date  
FROM (  
    SELECT 0 AS daynum UNION ALL  
    SELECT 1 UNION ALL  
    SELECT 2 UNION ALL  
    SELECT 3 UNION ALL  
    SELECT 4 UNION ALL  
    SELECT 5 UNION ALL  
    SELECT 6  
) AS a  
WHERE DATE_ADD(@start_of_week, INTERVAL a.daynum DAY) BETWEEN @start_of_week AND @end_of_week  
ORDER BY week_date;

SQL 逻辑分析：

1. 在这个查询中，我们创建了一个包含数字 0 到 6 的子查询 a，这些数字代表了一周中的天数。然后，我们使用 DATE_ADD() 函数将这些天数添加到一周的开始日期上，从而生成一周内的所有日期。
2. 注意事项：这种方法假设一周从星期日开始（WEEKDAY() 函数返回 0 表示星期日），如果你的设置或地区的一周开始日不同，你可能需要调整 WEEKDAY() 函数的用法或手动设置 @start_of_week 的值。
3. 这种方法比上述第一种【1】使用递归 CTE 或交叉连接更简单些，（缺点）但如果你需要处理更大的日期范围或更复杂的日期逻辑，那么可能需要考虑其他方法。

【3】方式三：直接SQL查询

-- 查询当前日期 CURDATE() 所在星期的所有日期列表枚举
-- 提醒：当让想得到指定日期所在星期的所有日期枚举，将 CURDATE() 替换成对应日期（例如"2024-01-01"）即可。
SELECT  
    DATE_ADD(  
        CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY,  
        INTERVAL a.n DAY  
    ) AS week_date  
FROM (  
    SELECT 0 AS n 
    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6  
) AS a  
WHERE  
    DATE_ADD(  
        CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY,  
        INTERVAL a.n DAY  
    ) BETWEEN CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY AND CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY  
ORDER BY week_date;

注意事项：

    WEEKDAY() 函数在 MySQL 中默认将星期日作为一周的第一天（返回值为 0），如果你的设置或地区的一周开始日不同，你可能需要调整计算方式。

    例如，如果星期一是一周的第一天，你可以将 WEEKDAY(CURDATE()) 替换为 WEEKDAY(CURDATE() + 1) % 7（假设 WEEKDAY() 返回 0 到 6，且星期日为 0）。

（3）MySQL8 得到虚拟指定【年】的所有日期

【1】方式一：MySQL8 递归公用表表达式(CTE) MySQL 查询

-- 获取指定日期（2023-01-01）所在年份的所有日期
WITH RECURSIVE DateSequence AS (  
    SELECT CAST('2023-01-01' AS DATE) AS date_val  
    UNION ALL  
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)  
    FROM DateSequence  
    WHERE DATE_ADD(date_val, INTERVAL 1 DAY) <= '2023-12-31'  
)
SELECT date_val  
FROM DateSequence  
ORDER BY date_val

扩展1：如果指定日期为 2023-12-30 开始呢？答案：只有【2023-12-30，2023-12-31】

扩展2：从【扩展1】可知，如果我需要一个季度又或者一个月度又或者一个星期的呢？答案：传入两个日期①一个开始日期；②一个结束日期 即可得到任意范围的日期列表。

【2】方式二：原生 SQL 查询

SELECT  
    ADDDATE('2024-01-01', INTERVAL a.i + (10 * b.i) + (100 * c.i) DAY) AS date_val  
FROM  
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  
     UNION ALL SELECT 8 UNION ALL SELECT 9) a  
CROSS JOIN  
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  
     UNION ALL SELECT 8 UNION ALL SELECT 9) b  
CROSS JOIN  
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c  
WHERE  
    -- 确保生成的日期在2023年内  
    ADDDATE('2024-01-01', INTERVAL a.i + (10 * b.i) + (100 * c.i) DAY) BETWEEN '2024-01-01' AND '2024-12-31'  
ORDER BY  
    date_val;

扩展1：可以传两个日期去控制生产的数据范围。

（4）MySQL8 得到虚拟指定【季度】的所有日期

【1】方式一：临时变量 & 联合查询

-- 假设我们要找的是2023年的第2季度（即4月到6月）  
SET @year = 2023;  
SET @quarter = 2;  
  
-- 计算季度的起始月份和天数  
SET @start_month = (@quarter - 1) * 3 + 1;  
SET @end_month = @start_month + 2; -- 因为一个季度有3个月  
  
-- 计算季度的起始日期和结束日期  
SET @start_date = CONCAT(@year, '-', LPAD(@start_month, 2, '0'), '-01');  
SET @end_date = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(@year, '-', LPAD(@end_month, 2, '0'), '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH) - INTERVAL 1 DAY, '%Y-%m-%d');  
  
-- 生成季度内的所有日期  
SELECT  
    ADDDATE(@start_date, INTERVAL a.i + (10 * b.i) DAY) AS date_val  
FROM  
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  
     UNION ALL SELECT 8 UNION ALL SELECT 9) a  
CROSS JOIN  
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  
     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7  
     UNION ALL SELECT 8 UNION ALL SELECT 9) b  
WHERE  
    ADDDATE(@start_date, INTERVAL a.i + (10 * b.i) DAY) BETWEEN @start_date AND @end_date  
ORDER BY  
    date_val;

测试结果：（2023年1季度：90天，2024年1季度：91天，理由是 2月份不同）

SQL 逻辑分析：

• 这个查询首先设置了年份和季度，然后计算了季度的起始月份和结束月份。接着，它计算了季度的起始日期和结束日期（结束日期是下一个月的第一天减去一天）。最后，它使用两个数字表（a和b）的交叉连接来生成这个范围内的所有日期。
• 注意事项：这个查询假设每个月都有相同的天数（即没有考虑到不同月份天数不同的情况），但它对于大多数情况应该足够好用了。如果你需要更精确的结果（例如，考虑到2月可能只有28天或29天），你可能需要编写更复杂的逻辑来逐月生成日期。

【2】方式二：MySQL8 with 使用

-- 使用时，将 year_param 和 quarter_param 替换为具体的年份和季度值 ，开始月份值（quarter_start_month）替换每个季度的其实月份即可得到完整的季度所有的日期 
-- 例如：  
-- SET @year_param = 2023;  -- 年份：2023年
-- SET @quarter_param = 1;  -- 季度：第一季度
-- SET @quarter_start_month = 1; -- 这个第一季度的开始月份为1月份
-- 然后将上述变量直接替换到 WITH RECURSIVE 语句中的 year_param 和 quarter_param 以及 quarter_start_month 。

WITH RECURSIVE DateSequence AS (  
    SELECT   
        STR_TO_DATE(CONCAT(year_param, '-', LPAD(quarter_start_month, 2, '0'), '-01'), '%Y-%m-%d') AS date_val,  
        LAST_DAY(STR_TO_DATE(CONCAT(year_param, '-', LPAD(quarter_start_month + 2, 2, '0'), '-01'), '%Y-%m-%d')) AS quarter_end_date  
    UNION ALL  
    SELECT   
        DATE_ADD(date_val, INTERVAL 1 DAY) AS date_val,  
        quarter_end_date  
    FROM DateSequence  
    WHERE DATE_ADD(date_val, INTERVAL 1 DAY) <= quarter_end_date  
)  
SELECT date_val  
FROM DateSequence  
WHERE YEAR(date_val) = year_param  
  AND QUARTER(date_val) = quarter_param  
ORDER BY date_val;  

• 【注意事项】 ：递归查询在 MySQL 中有递归深度的限制（默认为 1000） ，这可能会限制可以生成的日期的数量。但是，对于任何季度的日期生成来说，这个限制应该足够了。
• 如果需要生成更多的日期，可以考虑增加 MySQL 的 max_execution_time 和 cte_max_recursion_depth 设置（注意：cte_max_recursion_depth 是 MySQL 8.0.17 及以上版本中的新设置）。

---