CREATE TABLE calendar_date ( date_id DATE NOT NULL PRIMARY KEY, year_num SMALLINT NOT NULL, month_num TINYINT NOT NULL, day_num TINYINT NOT NULL, day_of_week VARCHAR(10) NOT NULL );
INSERT INTO calendar_date (date_id, year_num, month_num, day_num, day_of_week)
SELECT
dt AS date_id,
YEAR(dt) AS year_num,
MONTH(dt) AS month_num,
DAY(dt) AS day_num,
DAYNAME(dt) AS day_of_week
FROM (
SELECT DATE('2006-01-01') + INTERVAL seq DAY AS dt
FROM (
-- 약 20,000개의 숫자를 만드는 구간
SELECT
a.a + b.a * 10 + c.a * 100 + d.a * 1000 + e.a * 10000 AS seq
FROM
(SELECT 0 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 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
CROSS JOIN
(SELECT 0 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) c
CROSS JOIN
(SELECT 0 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) d
CROSS JOIN
(SELECT 0 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) e
) AS numbers
) AS dates
WHERE dt BETWEEN '2006-01-01' AND '2050-12-31'
ORDER BY dt;