How to Populate a MySQL Table with a Date Range using a Stored Procedure?

Generating a MySQL Date Range Table with a Stored Procedure

Often, you need a table populated with a sequence of dates within a specific range, for instance, from 2011-01-01 to 2011-12-31. A stored procedure offers an efficient solution.

The following stored procedure, filldates, uses a WHILE loop to iterate through the date range and insert each date into the _date column of your table:

<code class="language-sql">DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  DECLARE adate DATE;
  SET adate = dateStart;
  WHILE adate <= dateEnd DO
    INSERT INTO your_table (_date) VALUES (adate);
    SET adate = adate + INTERVAL 1 DAY;
  END WHILE;
END |
DELIMITER ;</code>

To populate your table, execute the filldates procedure, providing the start and end dates:

<code class="language-sql">CALL filldates('2011-01-01','2011-12-31');</code>

This method quickly creates the table containing all dates within the specified range.

To avoid duplicate entries, modify the procedure to check for existing dates before insertion:

<code class="language-sql">CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  DECLARE adate DATE;
  SET adate = dateStart;
  WHILE adate <= dateEnd DO
    IF NOT EXISTS (SELECT 1 FROM your_table WHERE _date = adate) THEN
      INSERT INTO your_table (_date) VALUES (adate);
    END IF;
    SET adate = adate + INTERVAL 1 DAY;
  END WHILE;
END;</code>

This revised procedure ensures data uniqueness. Remember to replace your_table with the actual name of your table.

The above is the detailed content of How to Populate a MySQL Table with a Date Range using a Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!