mysql 获取一段时间的日期集合或日历表

 近期存在一个老项目迁移调整的开发工作，主要工作量在把oracle数据切换至mysql，其中一下oracle特有的写法要调整为mysql，如：

SELECT to_date('2022-01-01','yyyy-mm-dd')+level-1 as JCSJ from dual
connect by level <= to_date('2022-02-01','yyyy-mm-dd') - to_date('2022-01-01','yyyy-mm-dd')+1

查询结果

 刚开始寻思找到mysql对应connect by level的语法，但无奈没找到，所以想到建议一个对应的日历表来实现对应的查询需求，但存在后续维护问题；

So，发现了这中写法，记录一下

--- 月历
SELECT DATE_FORMAT(DATE_ADD(
'2024-12-01',INTERVAL -(CAST(help_topic_id AS SIGNED INTEGER )) MONTH ),'%Y-%m') sdate
FROM mysql.help_topic
WHERE help_topic_id <= TIMESTAMPDIFF(MONTH,'2023-01-01','2024-12-01')  ORDER BY sdate ASC

运行结果

 

--- 日历
SELECT DATE_FORMAT(DATE_ADD(
'2023-02-09',INTERVAL -(CAST(help_topic_id AS SIGNED INTEGER )) DAY ),'%Y-%m-%d') sdate
FROM mysql.help_topic
WHERE help_topic_id <= TIMESTAMPDIFF(DAY,'2023-01-01','2023-02-09')  ORDER BY sdate ASC