MySql查询当月的每一天

（自用笔记整理）

查询当月的每一天，由1号开始，到当月结束日期

SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY), INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY ) DAY 
from mysql.help_topic
where help_topic_id  < day(last_day(curdate()))
order by help_topic_id

返回结果：

查询当天起未来一个月(30天)的每一天 

select 
date_add(curdate(), interval(cast(help_topic_id as signed integer) ) day) day
from mysql.help_topic
where help_topic_id  < day(last_day(curdate()))
order by help_topic_id

返回结果：

新添加另一种查询方法：

SELECT date FROM
(select
DATE_ADD(t1.dayed,INTERVAL t2.number DAY) date from
(select DATE_ADD(curdate(),interval -day(curdate())+1 day) dayed) t1,
(SELECT @xi:=@xi+1 as number from 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1, 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) xc2,
(SELECT @xi:=-1) xc0 ) t2 
where DATE_ADD(t1.dayed ,INTERVAL t2.number DAY))time WHERE SUBSTRING(date,1,7)=SUBSTRING(CURDATE(),1,7) 

结果： 

一切美好的遇见都是命中注定~！