需求背景
我們在工作中，經(jīng)常遇到這樣一個需求：需要某一年的所有自然日列表，或者某幾年的自然日列表。可惜的是，MySQL中沒有任何一個函數(shù)可以生成給出的兩個日期之間的所以自然日。
要想根據(jù)指定的兩個日期，得到這兩個日期之間所有的自然日，我們需要自己動手來實現(xiàn)。
創(chuàng)建存儲過程
我們需要使用MySQL的interval n day這個函數(shù)，然后通過存儲過程來實現(xiàn)。具體的示例如下：
/*定義SQL語句的分隔符為兩個美元符號*/
DELIMITER $$
/*如果存儲過程存在，刪除掉這個存儲過程*/
DROP PROCEDURE IF EXISTS create_calendar $$
/*創(chuàng)建存儲過程*/
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;
';
/*準(zhǔn)備DDL語句，然后執(zhí)行該DDL語句*/
prepare stmt from @create_sql;
execute stmt;
/*根據(jù)傳入的時間點，循環(huán)向表中插入日期*/
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$$ /*存儲過程創(chuàng)建結(jié)束*/
/*重新定義SQL語句的分隔符為分號*/
DELIMITER ;
調(diào)用存儲過程
創(chuàng)建完成上面的存儲過程之后，我們開始調(diào)用存儲過程，調(diào)用方式如下所示：
/*生成數(shù)據(jù)到calendar_custom表2021-01-01~2025-01-01之間的所有日期數(shù)據(jù)*/
CALL create_calendar ('2021-01-01', '2025-01-01');
驗證結(jié)果
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>
