mariadb 캘린더 테이블 생성하고 데이터 넣기
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
d.dt,
YEAR(d.dt),
MONTH(d.dt),
DAY(d.dt),
DAYNAME(d.dt)
FROM (
SELECT DATE_ADD('2051-01-01', INTERVAL units.n DAY) AS dt
FROM (
SELECT
a.n + b.n * 10 + c.n * 100 + d.n * 1000 + e.n * 10000 AS n
FROM
(SELECT 0 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 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
(SELECT 0 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 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
(SELECT 0 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 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
(SELECT 0 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 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
(SELECT 0 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 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e
) units
) d
WHERE d.dt BETWEEN '2051-01-01' AND '2100-12-31'
ORDER BY d.dt;