mysql数据库生成日期相关数据

share mysql数据库生成日期相关数据

无聊的业务中居然设计了一个日历数据，mysql生成年日历数据。

• PS：多么无聊～～～

table

CREATE TABLE `sys_calendar2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `d_date` date NOT NULL,
  `d_year` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d_month` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d_day` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d_week_times` int(11) NULL DEFAULT NULL,
  `d_week` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d_week_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d_ym` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 854 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

procedure

CREATE DEFINER = CURRENT_USER PROCEDURE `proc_make_calendar2`(IN `vyear` varchar(10))
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(vyear , '-01-01');
        set end_date = concat(cast(vyear AS UNSIGNED INTEGER)+1,'-01-01');	   
	set date_count = datediff(end_date,start_date);
	select end_date, start_date,date_count;
while i < date_count DO
	insert into sys_calendar2(d_date,d_year) -- ,d_month,d_day,d_week,d_week_name,d_ym)
	SELECT	start_date,vyear;
	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; 
update sys_calendar2 set d_week_times=WEEKOFYEAR(d_date), d_month=DATE_FORMAT(d_date,'%m'),d_day=DATE_FORMAT(d_date,'%d'),d_week=weekday(d_date)+1,d_ym=DATE_FORMAT(d_date,'%Y%m');
update sys_calendar2 , sys_week set d_week_name=weekname where d_week=sys_week.id;
END