如何让MYSQL生成指定时间段的日期

今天在头条看到作者“一缕82年的清风”发的一文章，文章标题是“MySQL统计近30天的数据，无数据的填充0”。虽然这个问题一般为了减少数据库的计算压力都会在业务代码上来处理。而不会让数据库来实现。但自己瞬间对这个问题感兴趣想研究看下还有没有别的解决办法来自动填充日期。

根据作者写的自动填充日期的SQL语句执行结果。分析思考还真想出两个解决办法供大家参考。

1、作者的SQL语句

SELECT 
	@s := @s + 1 AS indexs, DATE_FORMAT(DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)), '%Y-%m-%d') AS dates 
FROM 
	mysql.help_topic, 
	(SELECT @s := -1) temp 
WHERE 
	@s < 30 
ORDER BY dates 
LIMIT 30;

执行结果：
+--------+------------+
| indexs | dates      |
+--------+------------+
|     30 | 2022-02-28 |
|     29 | 2022-03-01 |
|     28 | 2022-03-02 |
|     27 | 2022-03-03 |
|     26 | 2022-03-04 |
|     25 | 2022-03-05 |
|     24 | 2022-03-06 |
|     23 | 2022-03-07 |
|     22 | 2022-03-08 |
|     21 | 2022-03-09 |
|     20 | 2022-03-10 |
|     19 | 2022-03-11 |
|     18 | 2022-03-12 |
|     17 | 2022-03-13 |
|     16 | 2022-03-14 |
|     15 | 2022-03-15 |
|     14 | 2022-03-16 |
|     13 | 2022-03-17 |
|     12 | 2022-03-18 |
|     11 | 2022-03-19 |
|     10 | 2022-03-20 |
|      9 | 2022-03-21 |
|      8 | 2022-03-22 |
|      7 | 2022-03-23 |
|      6 | 2022-03-24 |
|      5 | 2022-03-25 |
|      4 | 2022-03-26 |
|      3 | 2022-03-27 |
|      2 | 2022-03-28 |
|      1 | 2022-03-29 |
+--------+------------+
30 rows in set (0.00 sec)

2、新的解决办法1

SELECT 
	DATE(NOW()) + INTERVAL -t1.help_topic_id DAY 
FROM 
	mysql.help_topic t1 
WHERE 
	t1.help_topic_id > 0 
ORDER BY help_topic_id ASC 
LIMIT 30;

执行结果：
+----------------------------------------------+
| DATE(NOW()) + INTERVAL -t1.help_topic_id DAY |
+----------------------------------------------+
| 2022-03-29                                   |
| 2022-03-28                                   |
| 2022-03-27                                   |
| 2022-03-26                                   |
| 2022-03-25                                   |
| 2022-03-24                                   |
| 2022-03-23                                   |
| 2022-03-22                                   |
| 2022-03-21                                   |
| 2022-03-20                                   |
| 2022-03-19                                   |
| 2022-03-18                                   |
| 2022-03-17                                   |
| 2022-03-16                                   |
| 2022-03-15                                   |
| 2022-03-14                                   |
| 2022-03-13                                   |
| 2022-03-12                                   |
| 2022-03-11                                   |
| 2022-03-10                                   |
| 2022-03-09                                   |
| 2022-03-08                                   |
| 2022-03-07                                   |
| 2022-03-06                                   |
| 2022-03-05                                   |
| 2022-03-04                                   |
| 2022-03-03                                   |
| 2022-03-02                                   |
| 2022-03-01                                   |
| 2022-02-28                                   |
+----------------------------------------------+
30 rows in set (0.00 sec)

3、新的解决办法2

SELECT 
	DATE(NOW()) + INTERVAL -(t1.value + t2.value + t4.value + t8.value + t16.value) DAY AS dates 
FROM 
	(SELECT 0 AS value UNION ALL SELECT 1 value) t1 
	CROSS JOIN (SELECT 0 AS value UNION ALL SELECT 2 value) t2 
	CROSS JOIN (SELECT 0 AS value UNION ALL SELECT 4 value) t4 
	CROSS JOIN (SELECT 0 AS value UNION ALL SELECT 8 value) t8 
	CROSS JOIN (SELECT 0 AS value UNION ALL SELECT 16 value) t16 
WHERE 
	t1.value + t2.value + t4.value + t8.value + t16.value > 0;

执行结果：
+------------+
| dates      |
+------------+
| 2022-03-29 |
| 2022-03-28 |
| 2022-03-27 |
| 2022-03-26 |
| 2022-03-25 |
| 2022-03-24 |
| 2022-03-23 |
| 2022-03-22 |
| 2022-03-21 |
| 2022-03-20 |
| 2022-03-19 |
| 2022-03-18 |
| 2022-03-17 |
| 2022-03-16 |
| 2022-03-15 |
| 2022-03-14 |
| 2022-03-13 |
| 2022-03-12 |
| 2022-03-11 |
| 2022-03-10 |
| 2022-03-09 |
| 2022-03-08 |
| 2022-03-07 |
| 2022-03-06 |
| 2022-03-05 |
| 2022-03-04 |
| 2022-03-03 |
| 2022-03-02 |
| 2022-03-01 |
| 2022-02-28 |
| 2022-02-27 |
+------------+
31 rows in set (0.00 sec)

4、解决办法分析

• 作者SQL语句：利用MYSQL帮助表的记录+数字变量实现填充日期。
• 新解决办法1：根据作者SQL语句分析结合MYSQL帮助表字段help_topic_id自增有序值填充日期。减少了数字变量。
• 新解决办法2：考虑新解决办法1如果MYSQL帮助表字段help_topic_id不是自增有序值的话执行的数据结果就是bug。所以取掉查询实体表利用MySQL的交叉关联方式生成0~31个数字填充日期。

5、总结

• 个人推荐使用新解决办法2实现填充日期。SQL语句无任何表依赖。

感谢大家的评论、点赞、分享、关注。。。