MySQL中如何生成連續的自然日

發布時間：2023-07-03 01:42:18 作者：網友整理

需求背景

我們在工作中，經常遇到這樣一個需求：需要某一年的所有自然日列表，或者某幾年的自然日列表?？上У氖?，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>

分享到：

標簽：MySQL