mysql日期维度生成代码

-- 创建维度表
CREATE TABLE dim_date (
  `id` int(8) NOT NULL DEFAULT '0',
  `key_date` date NOT NULL DEFAULT '0000-00-00',
    `str_date` char(10) NOT NULL DEFAULT '0000-00-00',
  `year` int(4) NOT NULL,
  `quarter` int(1) NOT NULL,
  `month` int(2) NOT NULL,
  `week` int(1) NOT NULL COMMENT '星期',
  `weekofyear` int(2) NOT NULL COMMENT '一年中的第几周',
  `day` int(2) NOT NULL COMMENT '日',
  `dayofyear` int(3) NOT NULL COMMENT '一年总的第几天',
  PRIMARY KEY (`id`)
    )
    
    --  日期维度创建函数
delimiter //
DROP PROCEDURE IF EXISTS getAllDate; 
CREATE PROCEDURE getAllDate()
BEGIN
    DECLARE count1 int default 800;  -- 开始生成日期距今日天数
        DECLARE count2 int default 4000;    -- 截止日期距今天的天数
        DECLARE count int default 0;     -- 控制循环计数变量
        DECLARE startDay DATE ;
        set count = count1*(-1);
        set startDay = DATE_ADD(DATE(now()),INTERVAL count DAY);

  WHILE count<count2 DO
            INSERT INTO `dim_date`(`id`, `key_date`, str_date,`year`, `quarter`, `month`, `week`, `weekofyear`, `day`, `dayofyear`) 
                        VALUES (DATE_FORMAT(startDay,'%Y%m%d'), startDay,DATE_FORMAT(startDay,'%Y-%m-%d'), YEAR(startDay), QUARTER(startDay), MONTH(startDay), WEEKDAY(startDay)+1, week(startDay,1), DAY(startDay), DAYOFYEAR(startDay));
            set count = count +1;
            set startDay = DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
    END WHILE;
END//
delimiter ;

-- 执行函数
call getAllDate();

-- 查询结果 
select * from dim_date order by key_date;