MySQL日历表

建表

CREATE TABLE `attendance_calendar` (
  `dt` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '日期',
  `yyyy` int DEFAULT NULL COMMENT '年份',
  `mm` int DEFAULT NULL COMMENT '月份',
  `dd` int DEFAULT NULL COMMENT '日',
  `day_of_week` int DEFAULT NULL COMMENT '星期几',
  `week_num` int DEFAULT NULL COMMENT '第几周',
  `status` int DEFAULT '0' COMMENT '状态 1 需上学',
  `merchant_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '商户id',
  UNIQUE KEY `dt_merchantId` (`dt`,`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

建存储过程

CREATE PROCEDURE `create_date`(in year VARCHAR(20), in merchantId VARCHAR(32))
begin  
declare i int;
declare start_date varchar(20);
declare end_date varchar(20);
declare date_count int;

    set i=0;  
        set start_date= concat(year, '-01-01');
        set end_date = concat(year+1,'-01-01');
    DELETE from attendance_calendar where yyyy = year;
        set date_count = datediff(end_date, start_date);

    while i < date_count DO  
        INSERT into attendance_calendar (`dt`, `yyyy`, `mm`, `dd`, `day_of_week`, `week_num`, `status`, `merchant_id`)  
            SELECT  
                            DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') dt,  
                                DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') yyyy,
                                DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%m') mm,
                DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%d') dd, 
                DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) day_of_week,
                DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%u') weeknum,
                                if(DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) in (1,2,3,4,5), 1, 0) status,
                                merchantId  as merchant_id
        from dual;  
        set i=i+1;  
        set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');  
    end while;  
end

执行

call create_date('2020', '70');