Mysql生成任意指定两时间范围内的日期列表（三种方法）

╰半夏微凉° 2022-05-10 13:06 1690阅读 0赞

前言：工作当中有一个场景是数据库存了一些数据，但是日期是零碎的，只有当用户进行了相应日期的操作才会有对应记录的生成。此时有一个需求就是要让用户看到哪个日期有操作，哪个日期没有操作。此时就需要有一个表可以提供一段时间范围内的所有日期。

在看这篇文章以前呢，得明白几个知识点，存储过程（如何创建，如何调用，好处是什么）、变量（如何创建，赋值，使用）、DATE\_SUB、DATE\_FORMAT、

在网上查找资料，看到的有三种方法，总结如下

***一、创建存储过程***（即创建一个临时的日历表。我是这样理解的），

例一：

以下是我从别人的博客抄过来的例子，这个例子是将日期范围写在创建的逻辑当中，感兴趣的伙伴可以试一下执行，当执行了调用的语句可以查看自己数据库中是否多了一张表。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70][]

例二：

这是另外一个创建存储过程，不同于以上的例子，这个可以直接在调用的时候给时间范围。对于日期范围需要和用户交互，需要灵活更改的，这个方法更加适用

DELIMITER $$
    DROP PROCEDURE IF EXISTS create_calendar $$
    CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)
    BEGIN
     
    	SET @createSql = 'CREATE TABLE IF NOT EXISTS calendar (
                          `date` date NOT NULL,
    		       UNIQUE KEY `unique_date` (`date`) USING BTREE
                       )ENGINE=InnoDB DEFAULT CHARSET=utf8'; 
    	prepare stmt from @createSql; 
    	execute stmt; 
     
    	WHILE s_date <= e_date DO
    		INSERT IGNORE INTO calendar VALUES (DATE(s_date)) ;
    		SET s_date = s_date + INTERVAL 1 DAY ;
    	END WHILE ; 
     
    END$$
    DELIMITER ;
     
    CALL create_calendar ('2018-03-01', '2018-12-30');

总结：使用存储过程的优点网上随便一查就能查到，以上这两种方式都能生成一张临时表，里面存放着你自己指定的时间范围内的所有日期。根据自己业务需求，可以选取其中一种方法，生成临时表，再与你的数据进行各种操作得出某段时间范围内日期齐全的数据。

***二、（变量控制）指定数据条数，生成连续的数字或日期***

这个标题说得比较抽象了，我自己都不懂自己在表达什么。看例子吧

例一：

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc MONTH), '%Y-%m') as date
    FROM ( 
    			SELECT @xi:=@xi+1 as xc from 
    			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
    			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
    			(SELECT @xi:=0) xc0 
    ) xcxc

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70 1][]

以上代码的作用就是生成当月前25（包括当月）个月的日期。具体解释如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70 2][]

总结：在如上的例子当中，涉及到的知识点是变量，DATE\_SUB(),DATE\_FORMAT().使用以上方法的好处就是不用创建存储过程，也不涉及到任何表。缺点就是数据的条数控制并不灵活，不能和用户之间形成互动，即不能自定义日期区间，只能控制数据条数。

***三、（利用现有的表做操作）***

要求是这个表中的数据足够庞大，好处是不涉及存储过程，不涉及变量，且能自定义日期区间。

例一：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_16_color_FFFFFF_t_70][]

总结：这个是我选择的方法，因为业务需要，我不能用创建存储过程的方法，再加上我需要和用户做交互，即需要指定日期范围，而不是数据条数，所以，前两种方法都被排除了，我就选择的最后这种。

写在最后，以上三种方法基本上可以满足%80以上的伙伴的需求了，根据需要不同，平台不同，选择适合自己的方法。如有问题，欢迎指正，感激不尽，望共同进步。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70]: /images/20220505/5962997daf4344bc9832f19ed224da61.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70 1]: /images/20220505/b8a4c6d7003a44e6b6a70924dfdfaa9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_27_color_FFFFFF_t_70 2]: /images/20220505/34c891efcbb94df28ab2e707a5709e40.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0RhaV9BaXh5_size_16_color_FFFFFF_t_70]: /images/20220505/3f60de91dff94b14b82207213391c0da.png