需求背景

我们在工作中，经常遇到这样一个需求：需要某一年的所有自然日列表，或者某几年的自然日列表。可惜的是，MySQL中没有任何一个函数可以生成给出的两个日期之间的所以自然日。

要想根据指定的两个日期，得到这两个日期之间所有的自然日，我们需要自己动手来实现。

创建存储过程

我们需要使用MySQL的interval n day这个函数，然后通过存储过程来实现。具体的示例如下：

/*定义SQL语句的分隔符为两个美元符号*/
DELIMITER $$

/*如果存储过程存在，删除掉这个存储过程*/
DROP PROCEDURE IF EXISTS create_calendar $$

/*创建存储过程*/
CREATE PROCEDURE create_calendar (start_date DATE, end_date DATE)
BEGIN
  	/*定义日期表的DDL语句*/
    SET @create_sql = '
    CREATE TABLE IF NOT EXISTS calendar_day_list (
    	`calendar_day` date NOT NULL,
      UNIQUE KEY `unique_date` (`calendar_day`) USING BTREE
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ';
    /*准备DDL语句，然后执行该DDL语句*/
    prepare stmt from @create_sql;
    execute stmt;
    
    /*根据传入的时间点，循环向表中插入日期*/
    WHILE start_date <= end_date DO
        INSERT IGNORE INTO calendar_day_list VALUES (DATE(start_date));
        SET start_date = start_date + INTERVAL 1 DAY;
    END WHILE;
END$$ /*存储过程创建结束*/

/*重新定义SQL语句的分隔符为分号*/
DELIMITER ;

调用存储过程

创建完成上面的存储过程之后，我们开始调用存储过程，调用方式如下所示：

/*生成数据到calendar_custom表2021-01-01~2025-01-01之间的所有日期数据*/
CALL create_calendar ('2021-01-01', '2025-01-01');

验证结果

mysql> CALL create_calendar ('2021-01-01', '2025-01-01');
Query OK, 1 row affected (0.25 sec)

mysql> show tables;
+-------------------+
| Tables_in_xyz     |
+-------------------+
| calendar_day_list |
| demo              |
| dept_info         |
| emp_info          |
| feng              |
| test              |
| test_bak          |
+-------------------+
7 rows in set (0.00 sec)

mysql> select count(1) from calendar_day_list;
+----------+
| count(1) |
+----------+
|     1462 |
+----------+
1 row in set (0.00 sec)

mysql> select * from calendar_day_list limit 10;
+--------------+
| calendar_day |
+--------------+
| 2021-01-01   |
| 2021-01-02   |
| 2021-01-03   |
| 2021-01-04   |
| 2021-01-05   |
| 2021-01-06   |
| 2021-01-07   |
| 2021-01-08   |
| 2021-01-09   |
| 2021-01-10   |
+--------------+
10 rows in set (0.00 sec)

mysql>