MySQL数据库生成某一年的日历存储过程

DELIMITER $$
CREATE PROCEDURE `t_base_calender` ( IN YEAR VARCHAR ( 20 ) ) 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' );

SET date_count = datediff( end_date, start_date );
WHILE
    i < date_count DO
    INSERT INTO t_base_calender ( day_time, day_week, day_type ) SELECT
    DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y-%m-%d' ) day_time,
    DAYOFWEEK( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) day_week,
//注意这里的周几会多1天，如应该是周五，会是周六。需特殊处理
CASE
    DAYOFWEEK( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) 
    WHEN 6 THEN
    2 
    WHEN 7 THEN
    2 ELSE 1 
    END day_type 
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

改良之后：

 

DELIMITER $$
CREATE PROCEDURE `t_base_calender` ( IN YEAR VARCHAR ( 20 ) ) 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' );

SET date_count = datediff( end_date, start_date );
WHILE
    i < date_count DO
    INSERT INTO t_base_calender ( day_time, day_week, day_type ) SELECT
    DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y-%m-%d' ) day_time,
CASE
    DAYOFWEEK( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) - 1 
    WHEN 0 THEN
    7 ELSE DAYOFWEEK( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) - 1 
    END day_week,
CASE
    DAYOFWEEK( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) 
    WHEN 7 THEN
    2 
    WHEN 1 THEN
    2 ELSE 1 
    END day_type 
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 t_base_calender('2021'); 

删除存储过程：

DROP PROCEDURE t_base_calender;

注：

MySQL的存储过程语句之前要加 DELIMITER $$ 否则会报 declare 定义变量报错。