生成日期维度表

1、创建表
DROP TABLE IF EXISTS sys_date;
CREATE TABLE IF NOT EXISTS sys_date (
date VARCHAR ( 255 ) DEFAULT NULL,
month VARCHAR ( 255 ) DEFAULT NULL,
day VARCHAR ( 255 ) DEFAULT NULL,
quarter VARCHAR ( 255 ) DEFAULT NULL,
year VARCHAR ( 255 ) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;

2、建立日期维度数据生成的存储过程
CREATE DEFINER=root@% PROCEDURE pre_populate_date(IN start_dt date, IN end_dt date)
BEGIN
WHILE start_dt <= end_dt DO
INSERT INTO sys_date(date_sk, date, month, quarter, year, day)
VALUES
( NULL, start_dt, MONTH(start_dt), QUARTER(start_dt), YEAR (start_dt), DAY(start_dt));
SET start_dt = adddate( start_dt, 1 );
END WHILE;
COMMIT;
END

3、执行
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE sys_date;
CALL pre_populate_date ( ‘2021-01-01’, ‘2030-12-31’ );
SET FOREIGN_KEY_CHECKS = 1;