首页 > 数据库 > MySQL >

mysql创建日历表的方法

2018-03-10

mysql创建日历表的方法。日历表包括: 日期,星期,季度,月份,第几周。

日历表包括: 日期,星期,季度,月份,第几周。

表的结构 以及存储过程如下;

CREATE TABLE `dim_day` (  
  `ID` int(11) NOT NULL AUTO_INCREMENT,  
  `DAY_SHORT_DESC` datetime DEFAULT NULL,  
  `DAY_LONG_DESC` varchar(50) DEFAULT NULL,  
  `WEEK_DESC` varchar(20) DEFAULT NULL,  
  `WEEK_ID` varchar(20) DEFAULT NULL,  
  `WEEK_LONG_DESC` varchar(50) DEFAULT NULL,  
  `MONTH_ID` varchar(20) DEFAULT NULL,  
  `MONTH_LONG_DESC` varchar(50) DEFAULT NULL,  
  `QUARTER_ID` varchar(20) DEFAULT NULL,  
  `QUARTER_LONG_DESC` varchar(20) DEFAULT NULL,  
  `YEAR_ID` varchar(20) DEFAULT NULL,  
  `YEAR_LONG_DESC` varchar(50) DEFAULT NULL,  
  PRIMARY KEY (`ID`)  
) ENGINE=InnoDB AUTO_INCREMENT=731 DEFAULT CHARSET=utf8;  

存储过程:

删除 f_dim_day这个存储过程

DROP PROCEDURE f_dim_day;  
create procedure f_dim_day(in start_date VARCHAR(20),in date_count int)  
begin  
declare i int;  
    set i=0;  
    DELETE from dim_day;  
    while i < date_count DO  
        INSERT into dim_day (DAY_SHORT_DESC,DAY_LONG_DESC,WEEK_DESC,WEEK_ID,WEEK_LONG_DESC,MONTH_ID,MONTH_LONG_DESC,QUARTER_ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)  
            SELECT  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y-%m-%d&#39;) DAY_SHORT_DESC,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y年%m月%d日&#39;) DAY_LONG_DESC,  
                case DAYOFWEEK(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;))  when 1 then &#39;星期日&#39; when 2 then &#39;星期一&#39; when 3 then &#39;星期二&#39; when 4 then &#39;星期三&#39; when 5 then &#39;星期四&#39; when 6 then &#39;星期五&#39; when 7 then &#39;星期六&#39; end WEEK_DESC,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y%u&#39;) WEEK_ID,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y年第%u周&#39;) WEEK_LONG_DESC,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y%m&#39;) MONTH_ID,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y年第%m月&#39;) MONTH_LONG_DESC,  
                CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y&#39;),quarter(STR_TO_DATE( start_date,&#39;%Y-%m-%d %H:%i:%s&#39;))) QUARTER_ID,  
                CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y&#39;),&#39;年第&#39;,quarter(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;)),&#39;季度&#39;) QUARTER_LONG_DESC,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y&#39;) YEAR_ID,  
                DATE_FORMAT(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),&#39;%Y年&#39;) YEAR_LONG_DESC  
        from dual;  
        set i=i+1;  
        set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,&#39;%Y-%m-%d %H:%i:%s&#39;),interval 1 day),&#39;%Y-%m-%d&#39;);  
    end while;  
end;  

执行该存储过程:

call f_dim_day(&#39;2015-01-01&#39;,365);
相关文章
最新文章
热点推荐