mysql 生成一张全年日期表：工作日、周末、节假日

妖狐艹你老母 2022-02-19 02:01 1427阅读 0赞

> 这里用存储过程生成全年日期、工作日与周末；再通过更新数据状态为节假日

*  创建日期表
    
        CREATE TABLE `year_date_all` (
            `repDate` date NOT NULL COMMENT '日期',
            `repYear` int(8) NOT NULL COMMENT '年号',
            `repMonth` int(8) NOT NULL COMMENT '月',
            `repDay` int(8) NOT NULL COMMENT '天',
            `repWeek` int(8) NOT NULL COMMENT '周',
            `db_status` int(8) DEFAULT '0' COMMENT '0:工作日；1：周末休息日；2：法定休息日；3：调休工作日',
            PRIMARY KEY (`repDate`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全年日期表';
 *  存储过程插入数据

CREATE  PROCEDURE `getAllYearDateFunc`(in yaerParam int(8))
    BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE startDay DATE ;
    DECLARE yearNum INT DEFAULT 365;
    DECLARE workOrWeek INT DEFAULT 1;
    DECLARE yearBool INT DEFAULT 0;
    DECLARE db_status_ INT DEFAULT 0;
    // 判断是否数据存在
    SELECT count(*) INTO yearBool FROM year_date_all WHERE repYear=yaerParam;
    IF yearBool=0 THEN
    SET startDay = DATE(concat(yaerParam,'-01-01')); 
    //判断平闰年
    IF yaerParam  % 4 = 0 AND yaerParam  % 100 <> 0 OR yaerParam  % 400 = 0 THEN 
      SET yearNum = 366;
    ELSE
      SET yearNum = 365;
    END IF;
    
    WHILE count < yearNum DO
    SET  workOrWeek = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day));
     IF (workOrWeek)>5 THEN  
       SET db_status_=1;
     ELSE 
       SET db_status_=0;
     END IF;
     INSERT INTO year_date_all(repDate,repYear,repMonth,repDay,repWeek,db_status) VALUES(startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay),db_status_);
     SET count=count+1;
     SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
    END WHILE;
    
    END IF;
    END

*  效果图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]: /images/20220219/7b3e0281772a4ba8b5669861293c25c8.png