MySQL gets all the date points for a month

1. Questions

As the title says, get all the days of the month in a certain year through MySQL. such as getting all dates for February 2014.

2. Processing process

2.1 Creating a digital auxiliary table

CREATE TABLE `nums` (  `key` int(11) NOT NULL,  PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘数字辅助表‘;

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

DELIMITER $$CREATEdefiner= ' root ' @ '% ' PROCEDURE 'create_nums'(cnt int unsigned)BEGINdeclare s int unsigned default 1;TRUNCATE TABLE nums; Insert intoNumsSelectS whiles*2<=cnt Do        beginInsert intoNumsSelect' Key ' +s fromNumsSets=s*2;End;End  while;END$ $DELIMITER;

Execute stored procedure, increase 1-50000 to enter digital auxiliary table

call create_nums(50000);

2.3 Obtained from February 1, 2014 to February 31, 2014 through the Digital auxiliary table

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

2.3 The date in 2.2 that is less than or equal to the last day of February 2014 is the result we want.

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‘)) ;

MySQL gets all the date points for a month