MySQL LOOP

Summary: in this tutorial, you will learn how to use MySQL LOOP statement to run a block of code repeatedly based on a condition.

Introduction to MySQL LOOP statement

The LOOP statement allows you to execute one or more statements repeatedly.

Here is the basic syntax of the LOOP statement:

[begin_label:] LOOP
    statements;
END LOOP [end_label]Code language: SQL (Structured Query Language) (sql)

The LOOP can have optional labels at the beginning and end of the block.

Typically, you terminate the loop when a condition is true by using IF and LEAVE statements as follows:

[label]: LOOP
    ...
    -- terminate the loop
    IF condition THEN
        LEAVE [label];
    END IF;
    ...
END LOOP;Code language: SQL (Structured Query Language) (sql)

The loop exits when the LEAVE statement is reached.

Additionally, you can use the ITERATE statement to skip the current iteration and start a new one:

[label]: LOOP
    ...
    -- terminate the loop
    IF condition THEN
        ITERATE [label];
    END IF;
    ...
END LOOP;Code language: CSS (css)

Note that the LEAVE statement functions similarly to the break statement while the ITERATE statement works equivalently to the continue statement in other programming languages like PHP, C#, and Java.

MySQL LOOP statement example

First, create a table called calendars:

CREATE TABLE calendars (
    date DATE PRIMARY KEY,
    month INT NOT NULL,
    quarter INT NOT NULL,
    year INT NOT NULL
);Code language: PHP (php)

The calendars table has four columns:

• date – store the unique date.
• month – store the month of the date.
• quarter – store the quarter of the date.
• year – store the year of the date.

Second, define a stored procedure fillDates that inserts rows into the calendars table:

DELIMITER //

CREATE PROCEDURE fillDates(
	IN startDate DATE,
    IN endDate DATE
)
BEGIN
	DECLARE currentDate DATE DEFAULT startDate;
    
	insert_date: LOOP
		-- increase date by one day
		SET currentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);
        
        -- leave the loop if the current date is after the end date
        IF currentDate > endDate THEN
			LEAVE insert_date;
        END IF;
        
        -- insert date into the table
        INSERT INTO calendars(date, month, quarter, year)
        VALUES(currentDate, MONTH(currentDate), QUARTER(currentDate), YEAR(currentDate));
		
    END LOOP;
END //

DELIMITER ;Code language: PHP (php)

The stored procedure takes two input parameters, startDate and endDate, representing the beginning and end of the date range.

The stored procedure uses a loop, incrementing the currentDate by one day in each iteration until it reaches the endDate.

During each iteration, the stored procedure inserts the current date into the calendars table, along with the corresponding month, quarter, and year information.

The loop is terminated when the current date exceeds the specified end date.

Third, insert the dates from January 1st, 2024 to December 31st, 2024 into the calendars table by calling the fillDates stored procedure:

CALL fillDates('2024-01-01','2024-12-31');Code language: JavaScript (javascript)

Finally, retrieve the number of rows from the calendars table to verify the inserts:

SELECT COUNT(*) FROM calendars;

Output:

+----------+
| COUNT(*) |
+----------+
|      365 |
+----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

And retrieve some sample rows from the calendars table:

SELECT 
  * 
FROM 
  calendars 
ORDER BY 
  date DESC 
LIMIT 
  5;

Output:

+------------+-------+---------+------+
| date       | month | quarter | year |
+------------+-------+---------+------+
| 2024-12-31 |    12 |       4 | 2024 |
| 2024-12-30 |    12 |       4 | 2024 |
| 2024-12-29 |    12 |       4 | 2024 |
| 2024-12-28 |    12 |       4 | 2024 |
| 2024-12-27 |    12 |       4 | 2024 |
+------------+-------+---------+------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Note that you can use generated columns to automatically generate the data for the month, quarter, and year columns based on the values of the date column.

Summary

• Use the MySQL LOOP statement to execute a block of code repeatedly.