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 定义变量报错。
