MySQL用存储过程创建日期字典表

原创

dm3344 2022-06-30 15:00:36 博主文章分类：Mysql ©著作权

©著作权归作者所有：来自51CTO博客作者dm3344的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建表：

CREATE TABLE DIC_DATE(
DATE             DATE
,STR_DATE        VARCHAR(16)
,LOCAL_DATE      VARCHAR(16)
,YEAR            INT
,MONTH           INT
,DAY             INT
,DAYOFYEAR       INT
,DAYOFMONTH      INT
,WEEKOFYEAR      INT
,WEEKOFMONTH     INT
,CREATE_DATETIME TIMESTAMP DEFAULT NOW() COMMENT '数据创建日期'
) COMMENT '日期字典表' ;

创建存储过程（test 改自己数据库名）：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `test`.`PRO_DATE_TABLE`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    DECLARE V_START_DATE   DATE  DEFAULT DATE_FORMAT('2018-01-01','%Y-%m-%d');
    WHILE V_START_DATE <= DATE_FORMAT('2025-12-31','%Y-%m-%d') DO
       INSERT INTO DIC_DATE(
               DATE          
               ,STR_DATE     
               ,LOCAL_DATE   
               ,YEAR         
               ,MONTH        
               ,DAY          
               ,DAYOFYEAR  
               ,DAYOFMONTH 
               ,WEEKOFYEAR 
               ,WEEKOFMONTH
       )SELECT V_START_DATE                       DATE 
               ,CONCAT(V_START_DATE,'')           STR_DATE 
               ,CONCAT(YEAR(V_START_DATE)  ,'年'
                       ,MONTH(V_START_DATE),'月'
                       ,DAY(V_START_DATE)  ,'日') LOCAL_DATE
               ,YEAR(V_START_DATE)                YEAR
               ,MONTH(V_START_DATE)               MONTH
               ,DAY(V_START_DATE)                 DAY
               ,DAYOFYEAR(V_START_DATE)           DAYOFYEAR 
               ,DAYOFMONTH(V_START_DATE)          DAYOFMONTH
               ,WEEKOFYEAR(V_START_DATE)          WEEKOFYEAR
               ,WEEK(V_START_DATE, 5)- WEEK(DATE_SUB(V_START_DATE, INTERVAL DAYOFMONTH(V_START_DATE) - 1 DAY), 5) + 1 WEEKOFMONTH
          FROM DUAL;
          SET V_START_DATE = DATE_ADD(V_START_DATE,INTERVAL 1 DAY) ;
    END WHILE;
    END$$

DELIMITER ;