MySQL WHILE Loop: Syntax, Use Cases & Examples

What is a WHILE loop in MySQL?

The WHILE loop in MySQL repeatedly executes a block of statements as long as a specified condition remains true. Before each iteration, the loop evaluates the condition and continues running until the condition becomes false. Because it checks the condition directly at the start, the WHILE loop is the most widely used loop type.

The WHILE loop is used inside stored procedures and functions when we need to repeat a task until it meets a certain condition. Its advantages are simplicity, readability, effective prevention of infinite loops, and avoidance of unnecessary iterations since it only runs when the condition is true. In addition, its syntax makes it easier for software and database developers to learn and apply it in MySQL.

MySQL WHILE loop syntax

The basic syntax of the WHILE loop in MySQL is as follows:

WHILE condition DO
    statements;
END WHILE;

Parameters:

• WHILE condition DO - starts the WHILE loop
• condition - the logical expression evaluated before each iteration
• statements - the set of statements executed repeatedly while the condition is true
• END WHILE - marks the end of the loop

If the condition is true, MySQL executes the statements. If it evaluates to false, the loop ends.

Differences between WHILE and other loops in MySQL

The WHILE loop is not the only loop type in MySQL, but it is the most commonly used one due to its convenience. Let us consider other loop types and their characteristics.

Using WHILE loop in stored procedures

In MySQL, the WHILE loop is often used within stored procedures to control execution flow. It runs only while its condition evaluates to true, stopping immediately once the condition becomes false; if the condition is false from the start, the loop won't execute at all.

This makes WHILE loops useful for automating repetitive tasks and handling row-by-row or sequential processing within stored procedures.

Let us see how the WHILE loop works. To demonstrate this, we'll use a popular MySQL test database, sakila, and dbForge Studio for MySQL - a powerful IDE for MySQL and MariaDB that provides all the necessary functionality to cover various database-related tasks.

Let us insert the first 10 film IDs and their titles from the film table into a temporary table for demonstration. We create the following stored procedure for that:

DELIMITER //

CREATE PROCEDURE insert_film_ids_titles()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE max_id INT;

    -- Find the highest film_id in Sakila.film
    SELECT MAX(film_id) INTO max_id FROM film;

    -- Create a temporary table to store IDs and titles
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_film_info (
        film_id INT PRIMARY KEY,
        title VARCHAR(255)
    );

    -- Loop until counter exceeds 10 or max_id
    WHILE counter <= 10 AND counter <= max_id DO
        INSERT INTO temp_film_info (film_id, title)
        SELECT film_id, title
        FROM film
        WHERE film_id = counter;

        SET counter = counter + 1;
    END WHILE;

    -- Show the results
    SELECT * FROM temp_film_info;

END //

DELIMITER ;

To run the stored procedure, execute:

CALL insert_film_ids_titles();

As you can see, the loop allowed us to successfully insert the first 10 film_id and title pairs into the temp_film_info table.

WHILE loop in MySQL without stored procedures

MySQL, along with other major RDBMSs, allows using WHILE loops inside procedural code (e.g., stored procedures, functions, triggers). But is it possible to use loops in plain SQL queries?

MySQL is the strictest RDBMS in this aspect, and it does not allow using WHILE loops in a raw query. You need procedural wrappers for it (WHILE...DO blocks in stored procedures or functions).

MySQL WHILE loop in SELECT statements

As we already defined, WHILE loops can't run in SELECT statements in MySQL. If you need a similar behavior, you need to simulate it with other means, such as recursive CTEs or variables. Let us see some examples.

Recursive CTEs

MySQL 8.0 and later enable you to simulate loop behavior directly within a query. Assume we want to increase the rental rate of the first five films by $1.00. The WHILE loop in a procedure would do this, but in our scenario, we don't want to create a separate stored procedure for that task. Instead, we construct the following standalone query:

-- Step 1: Generate film IDs 1-5 using a recursive CTE
WITH RECURSIVE film_seq AS (
    SELECT 1 AS film_id
    UNION ALL
    SELECT film_id + 1 FROM film_seq WHERE film_id < 5
)
-- Step 2: Perform the loop for update
UPDATE film f
JOIN film_seq fs ON f.film_id = fs.film_id
SET f.rental_rate = f.rental_rate + 1.00;

First, the CTE generates film ID numbers 1-5 like a loop counter. Then, the JOIN clause filters the film table to only involve the necessary IDs. The update is not row-by-row, as MySQL applies changes in bulk. You can apply this approach to any number of IDs; just change the CTE limit.

Using variables

Another approach to getting the loop-like behavior without using the stored procedure containing a WHILE loop is using variables. However, it won't be a real loop; this method applies sequential actions across rows.

Suppose we need to assign a sequential number to the first five films (like a loop counter). The query is as follows:

SET @i := 0;

SELECT (@i := @i + 1) AS seq_no, f.film_id, f.title
FROM film f
ORDER BY f.film_id
LIMIT 5;

In this statement, @i simulates a counter increment per row, which is similar to WHILE. LIMIT 5 restricts it to 5 iterations. This query delivers the desired result, but it is necessary to note that the method is non-standard, and MySQL will not support it in future versions.

Common issues and how to fix them

WHILE loops can be easily misused in SQL, especially within stored procedures. Let's consider the most frequent mistakes and practical ways to recognize and fix them.

Infinite loop issues

When the loop condition is defined incorrectly, it never becomes false. Therefore, the procedure never finishes, consuming resources, while queries simply get stuck. You always need to check and modify a variable that affects the loop condition!

Have a look at the example procedure below:

DELIMITER $$

CREATE PROCEDURE infinite_loop_example()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total_actors INT;

    -- Get total number of actors
    SELECT COUNT(*) INTO total_actors FROM sakila.actor;

    -- Problem: counter never increments, so the condition is always true
    WHILE counter <= total_actors DO
        SELECT actor_id, first_name, last_name
        FROM sakila.actor
        WHERE actor_id = counter;
    END WHILE;
END$$

DELIMITER ;

In this procedure, the counter variable is 1, and it does not get incremented inside the loop. Therefore, the condition specified in the WHILE loop always remains true. As a result, an infinite loop appears.

To fix the issue, we need to modify the counter:

-- Increment the counter to avoid an infinite loop
SET counter = counter + 1;

The correct procedure code is:

DELIMITER $$

CREATE PROCEDURE fixed_loop_example()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total_actors INT;

    -- Get total number of actors
    SELECT COUNT(*) INTO total_actors FROM sakila.actor;

    -- Loop through all actor IDs
    WHILE counter <= total_actors DO
        SELECT actor_id, first_name, last_name
        FROM sakila.actor
        WHERE actor_id = counter;

        -- Increment the counter to avoid an infinite loop
        SET counter = counter + 1;
    END WHILE;
END$$

DELIMITER ;

This procedure will perform correctly.

Variable initialization errors

Another common error that may occur when using the WHILE loop is improperly declaring or initializing variables. As a result, we get NULL values or other inappropriate behavior of the procedure.

Assume we want to sum the lengths of film titles from the film table. We create a procedure that utilizes the WHILE loop for this purpose. However, the code does not suggest initializing the accumulator variable total_length:

DELIMITER $$

CREATE PROCEDURE while_loop_error()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total_films INT;
    DECLARE total_length INT; 
    DECLARE film_title_length INT;

    SELECT COUNT(*) INTO total_films FROM sakila.film;

    WHILE counter <= total_films DO
        -- Get the length of the film title for the current film_id
        SELECT LENGTH(title) INTO film_title_length
        FROM sakila.film
        WHERE film_id = counter;

        -- Problem: total_length is NULL, so adding anything to it stays NULL
        SET total_length = total_length + film_title_length;

        SET counter = counter + 1;
    END WHILE;

    SELECT total_length AS total_title_length;
END$$

DELIMITER ;

Since the total_length variable is declared but not initialized, it is NULL, causing all other results to be NULL.

CALL while_loop_error();

To fix this issue, we need to initialize the problematic total_length value. We set it to 0 before the loop:

DELIMITER $$

CREATE PROCEDURE while_loop_fixed()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total_films INT;
    DECLARE total_length INT DEFAULT 0;
    DECLARE film_title_length INT;

    SELECT COUNT(*) INTO total_films FROM sakila.film;

    WHILE counter <= total_films DO
        SELECT LENGTH(title) INTO film_title_length
        FROM sakila.film
        WHERE film_id = counter;

        SET total_length = total_length + film_title_length;

        SET counter = counter + 1;
    END WHILE;

    SELECT total_length AS total_title_length;
END$$

DELIMITER ;

After this modification, the loop correctly calculates the sum of the lengths of all film titles in the film table.

CALL while_loop_fixed();

Missing DELIMITER

Earlier in this article, we mentioned this issue. In MySQL, the default statement delimiter is a semicolon; however, a WHILE loop (or any BEGIN...END block) contains multiple statements separated by semicolons. If we keep the default delimiter before defining a procedure or loop, MySQL will consider the first ; symbol to end the entire statement, causing a syntax error.

To fix it, we need to set another delimiter for the procedure, such as $$:

DELIMITER $$

CREATE PROCEDURE while_with_delimiter()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE total INT;

    SELECT COUNT(*) INTO total FROM sakila.actor;

    WHILE counter <= total DO
        SELECT actor_id, first_name, last_name
        FROM sakila.actor
        WHERE actor_id = counter;

        SET counter = counter + 1;
    END WHILE;
END$$

DELIMITER ;

In this stored procedure, DELIMITER $$ means that MySQL should treat all code up to $$ as a single statement. It means that we can freely use semicolons inside the BEGIN...END block. END$$ terminates the procedure.

The MySQL GUI we use, dbForge Studio for MySQL, fixes this issue on the fly, saving the stored procedure with the correct delimiter automatically.

How dbForge Studio for MySQL simplifies WHILE loop debugging

This article used dbForge Studio for MySQL to illustrate the usage of the WHILE loop, as we wrote SQL queries using the SQL Editor and executed them against the sakila test database directly.

However, the Studio is a much more powerful solution, a multi-featured IDE that covers all database tasks in MySQL and MariaDB, from code writing to version controlling. Among the many helpful tools that dbForge Studio for MySQL comprises, we can name a built-in MySQL Debugger.

This tool is indispensable for debugging MySQL stored procedures, functions, triggers, and all other queries. Its functionality allows the developers to perform the following tasks:

• Set breakpoints
• Step through individual statements
• Add watches
• Evaluate and change local variable values
• Manage breakpoints

Let us see how to debug MySQL stored procedures using this tool. In Database Explorer, select the necessary procedure, right-click it, and select Compile > Compile for Debugging.

Then, proceed to start the debugging process.

Conclusion

WHILE loops are widely used in many scenarios, and MySQL specialists rely on them regularly. However, they can be tricky in stored procedures, so it is important to understand how to use them correctly, what issues may arise, and how to fix them. This article focused on those aspects, highlighting the successful scenarios and ways to detect and fix possible issues.

dbForge Studio for MySQL is a powerful tool that helps you create, analyze, and debug the WHILE loop code blocks in stored procedures, though it is just a small part of its robust functionality. You can try the full power of the Studio in action in your daily scenarios with a fully functional free trial of the Studio that is available for 30 days. Utilize all its capabilities under your actual workload!

FAQ