MySQL obtains all the date points of a month.

MySQL obtains all the date points of a month.
1. Problems

As the title says, use MySQL to get all the days of a month in a certain year. For example, obtain all dates of January 1, February 2014.

2. handling process

2.1 create a digital auxiliary table

Create table 'nums' ('key' int (11) not null, PRIMARY key ('key') ENGINE = InnoDB default charset = utf8 COMMENT = 'Number auxiliary table ';

2.2 create a stored procedure to add data to a digital auxiliary table

DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `create_nums`(cnt int unsigned)BEGINdeclare s int unsigned default 1; truncate table nums; insert into nums select s; while s*2<=cnt do begin insert into nums select `key`+s from nums; set s=s*2; end; end while;END$$DELIMITER ;

Execute the stored procedure and Add 1-to the digital auxiliary table

call create_nums(50000);

2.3 obtain data from January 1, February 1-20, 2014 to January 1, February 31 through the digital auxiliary table

select CONCAT('2014-02-',lpad(n.key,2,'0') ) day from nums n where n.key < 32

The data obtained in 2.3 is less than the date equal to the last day of 2.2.

select * from (select CONCAT('2014-02-',lpad(n.key,2,'0') ) day from nums n where n.key < 32) d where d.day <= last_day(DATE_FORMAT('2014-02-01','%Y-%m-%d')) ;