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;