MySQL WHILE Loop

Master the MySQL WHILE loop — pre-test loop where the condition is checked before each iteration, body may run zero times. Covers counter loops, accumulator patterns, early exit with LEAVE, and the WHILE vs REPEAT vs LOOP comparison for choosing the right tool.

The WHILE loop repeatedly runs a block of code as long as a condition stays true. It's a pre-test loop — the condition is checked before each iteration, so if the condition is false from the start, the body never runs even once.

WHILE is the right choice when you want the loop to run zero or more times and termination naturally fits a top-of-loop check, like "while there are still rows to process" or "while the counter is less than the limit."

1. Check the condition.
2. If false → exit the loop, jump to whatever comes after END WHILE.
3. If true → run the body once.
4. Go back to step 1.

The crucial part is step 2: because the test runs first, if it's already false on entry, the body is skipped entirely. That's the difference from REPEAT, which always runs the body at least once.

Print numbers 0 through 9 — the canonical "for i in range(10)" equivalent:

DROP PROCEDURE IF EXISTS while_loop_demo;

DELIMITER $$

CREATE PROCEDURE while_loop_demo()
BEGIN
    DECLARE i        INT DEFAULT 0;
    DECLARE maxCount INT DEFAULT 10;

    WHILE i < maxCount DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL while_loop_demo();

Notice the loop ran 10 times (i = 0 through i = 9) and stopped exactly when i hit 10 — the condition i < 10 became false at the top of what would have been the 11th iteration.

A more practical pattern — build up a comma-separated list of numbers using WHILE, returning a single string:

DROP PROCEDURE IF EXISTS build_csv;

DELIMITER $$

CREATE PROCEDURE build_csv(IN  upto INT,
                           OUT csv  VARCHAR(200))
BEGIN
    DECLARE i INT DEFAULT 1;
    SET csv = '';

    WHILE i <= upto DO
        IF csv = '' THEN
            SET csv = CAST(i AS CHAR);
        ELSE
            SET csv = CONCAT(csv, ',', i);
        END IF;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

CALL build_csv(7, @list);
SELECT @list;

To demonstrate the pre-test behavior, call the same procedure with upto = 0:

CALL build_csv(0, @list);
SELECT @list;

Empty string. Because i = 1 and upto = 0, the condition i <= upto was false on entry — the loop body never ran. This is the defining behavior of WHILE.

Sometimes you want to break out of a WHILE early — not because the main condition became false, but because some other event happened. LEAVE works on any labeled loop, WHILE included:

DROP PROCEDURE IF EXISTS find_first_high;

DELIMITER $$

CREATE PROCEDURE find_first_high()
BEGIN
    DECLARE i      INT DEFAULT 1;
    DECLARE found  INT;

    my_while: WHILE i <= 100 DO
        IF i * i > 5000 THEN
            SET found = i;
            LEAVE my_while;        -- exit early
        END IF;
        SET i = i + 1;
    END WHILE my_while;

    SELECT found AS first_n_with_square_over_5000;
END$$

DELIMITER ;

CALL find_first_high();

71² = 5041, the first square > 5000. The WHILE bailed at i = 71 instead of running all the way to 100.

1. Make sure something inside the loop changes the condition. The most common WHILE bug is forgetting to increment the counter or update the condition variable — instant infinite loop.
2. Default to WHILE for counter and "drain a queue" patterns. Use REPEAT only when you genuinely need at-least-once execution; use LOOP only when termination doesn't fit a top-of-loop test.
3. Label the loop if you need LEAVE or ITERATE, or any time you have nested loops.
4. Don't use WHILE to do something a single set-based SQL statement could do. A loop that runs UPDATE ... WHERE id = i for each row is much slower than a single UPDATE ... WHERE id IN (...).
5. Watch for infinite loops on bad input. Add a safety counter (e.g. IF iterations > 10000 THEN LEAVE my_while; END IF;) when the termination depends on data you can't fully trust.

• WHILE condition DO ... END WHILE is a pre-test loop — the condition is checked before each iteration, so the body may run zero times.
• Best for counter loops and "while there's still work to do" patterns.
• If the condition is false on entry, the body never executes — the canonical zero-iteration case.
• Always make sure something inside the body eventually makes the condition false; otherwise it's an infinite loop.
• Use LEAVE label for early exit; pair with ITERATE label for "continue" semantics.