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