Mysql生成日期（1）

原创已于 2025-10-11 15:58:19 修改 · 533 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-09-10 10:48:41 首次发布

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

WITH RECURSIVE DateSequence AS (
    SELECT CAST('2024-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) <= '2024-12-31'
)
SELECT date_val
FROM DateSequence
ORDER BY date_val

方式二：使用CROSS JOIN形成笛卡尔积，产生00到99的数字对。然后通过在给定的起始日期（2023-01-01）上加上这些组合起来的天数，产生了一系列日期。WHERE子句确保只选择1到31号的日期。最后通过ORDER BY对结果进行排序。

SELECT
    DATE_FORMAT('2023-01-01' + INTERVAL a.a + b.a DAY, '%Y-%m-%d') AS 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 UNION ALL SELECT 8 UNION ALL SELECT 9) 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) b
WHERE
    DATE_FORMAT('2023-01-01' + INTERVAL a.a + b.a DAY, '%d') BETWEEN 1 AND 31
ORDER BY date;