如何填充一系列日期的表格？ [英] How to populate a table with a range of dates?

问题描述

我需要一个MySQL表来保持2011-01-01和2011-12-31之间的所有日期。我创建了一个列，名称为_date，类型为DATE。

I need a MySQL table to hold ALL DATES between 2011-01-01 and 2011-12-31. I have created a table with one column names "_date", type DATE.

使用什么查询可以填充所有所需日期的表（而不必输入他们用手）？

With what query can I populate the table with all the desired dates (instead of having to enter them by hand)?

推荐答案

尝试这样：

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

以下是使用SQL小提琴：http://sqlfiddle.com/#!2/65d13/1

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/65d13/1

EDIT（to检查日期是否存在），如 Andrew Fox 。

EDIT (to check if date already exists) as asked by Andrew Fox.

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

    WHILE dateStart <= dateEnd DO

        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);

        IF adate IS NULL THEN BEGIN

            INSERT INTO MyDates (mydate) VALUES (dateStart);

        END; END IF;

        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

    END WHILE;

END;//

这里是使用SQL小提琴： http://sqlfiddle.com/#!2/66f86/1

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/66f86/1

这篇关于如何填充一系列日期的表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！