MySQLでカレンダーを作る

たまーにあると便利な奴。
再帰CTEで年・月・日をそれぞれ作って、CONCATで結合。
最後に日付として有効かチェックする

WITH RECURSIVE YEARS AS (
    SELECT
        2000 AS N
    UNION ALL
    SELECT
        N + 1
    FROM
        YEARS
    WHERE
        N < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR), '%Y')  -- 今年まで 
), MONTHS AS (
    SELECT
        1 AS N
    UNION ALL
    SELECT
        N + 1
    FROM
        MONTHS
    WHERE
        N < 12
), DAYS AS (
    SELECT
        1 AS N
    UNION ALL
    SELECT
        N + 1
    FROM
        DAYS
    WHERE
        N < 31
), YYYY AS (
    SELECT CAST(N AS CHAR) AS STR FROM YEARS
), MM AS (
    SELECT LPAD(CAST(N AS CHAR), 2, '0') AS STR FROM MONTHS
), DD AS (
    SELECT LPAD(CAST(N AS CHAR), 2, '0') AS STR FROM DAYS
), CONC AS (
    SELECT
        CONCAT(Y.STR, M.STR, D.STR) AS YMD
    FROM
        YYYY AS Y
        CROSS JOIN MM AS M
        CROSS JOIN DD AS D
), CALENDAR AS (
    SELECT YMD FROM CONC
    WHERE DATE_FORMAT(CAST(YMD AS DATETIME), '%Y%m%d') = YMD
)

SELECT * FROM CALENDAR ORDER BY YMD;