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

找到一个博客，发现运行不了，小小的改动了一下，最后面会标明博客出处，方便自己以后查看使用

创建日期表

   CREATE TABLE `config_calendar` (
    `rep_date` date NOT NULL COMMENT '日期',
    `rep_year` int(8) NOT NULL COMMENT '年号',
    `rep_month` int(8) NOT NULL COMMENT '月',
    `rep_day` int(8) NOT NULL COMMENT '天',
    `rep_week` int(8) NOT NULL COMMENT '周',
    `rep_status` int(8) DEFAULT '0' COMMENT '0:工作日；1：周末休息日；2：法定休息日；3：调休工作日',
    `enable_flag` int(8) NOT NULL COMMENT '逻辑删除，初始化1',
    PRIMARY KEY (`repDate`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全年日期表';

存储过程插入数据

DROP PROCEDURE IF EXISTS getAllYearDateFunc;
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 config_calendar WHERE rep_year=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 config_calendar(rep_date,rep_year,rep_month,rep_day,rep_status,rep_week,enable_flag) VALUES(startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),db_status_,WEEKDAY(startDay)+1,1);
 SET count=count+1;
 SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
END WHILE;

END IF;
END

执行储存过程 想要初始化那一年，括号里面改成那一年

call getAllYearDateFunc(2020)

执行过程中报错：[Err] 1304 - PROCEDURE getAllYearDateFunc already exists，在执行过程最前面加：

DROP PROCEDURE IF EXISTS getAllYearDateFunc;

如果表中有主键id，执行一个触发器

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `admin_center`.`id` -- 触发器名称
    BEFORE INSERT             -- 触发器被触发的时机
    ON `admin_center`.`config_calendar`       -- 触发器所作用的表名称
    FOR EACH ROW BEGIN
		SET new.id=REPLACE(UUID(),'-',''); -- 触发器执行的逻辑
    END$$

DELIMITER ;

原文链接：https://blog.csdn.net/qq_31424825/article/details/89311540