MySQL REPEAT Loop

Master the MySQL REPEAT loop — post-test loop where the body always runs at least once before the UNTIL condition is checked. Covers the inverted condition polarity vs WHILE, the no-semicolon-after-UNTIL syntax quirk, the at-least-once pattern, and when REPEAT is the right choice over WHILE or LOOP.

The REPEAT statement is MySQL's post-test loop. The body runs first, then the termination condition is checked. If the condition is false, the loop iterates again; if true, the loop exits.

The defining feature: the body always runs at least once, regardless of the condition. That's the practical difference from WHILE — use REPEAT when there's something you must do once before deciding whether to repeat.

Note the structure:

• The body sits between REPEAT and UNTIL.
• The condition follows UNTIL — and unlike most SQL statements, there's no semicolon after the condition. The next semicolon goes after END REPEAT.
• Loop continues while the condition is false; exits when it becomes true. (Opposite logic to WHILE, which continues while true.)

1. Run the body once.
2. Evaluate the UNTIL condition.
3. If true → exit the loop, jump to whatever comes after END REPEAT.
4. If false → go back to step 1.

Compare with WHILE, where the test is at the top: REPEAT's test is at the bottom, which is why the body always runs at least once.

Concatenate the numbers 1 through 9 into a single string. The body always needs to add at least one number, so REPEAT fits naturally:

DROP PROCEDURE IF EXISTS RepeatDemo;

DELIMITER $$

CREATE PROCEDURE RepeatDemo()
BEGIN
    DECLARE counter INT          DEFAULT 1;
    DECLARE result  VARCHAR(100) DEFAULT '';

    REPEAT
        SET result  = CONCAT(result, counter, ',');
        SET counter = counter + 1;
    UNTIL counter >= 10
    END REPEAT;

    SELECT result;
END$$

DELIMITER ;

CALL RepeatDemo();

The body ran 9 times (counter = 1, 2, 3, ..., 9). After the 9th iteration, counter became 10, the condition counter >= 10 was true, and the loop exited.

Show what makes REPEAT distinct from WHILE: the body always runs once, even if the termination condition is true on entry:

DROP PROCEDURE IF EXISTS at_least_once;

DELIMITER $$

CREATE PROCEDURE at_least_once()
BEGIN
    DECLARE n INT DEFAULT 100;       -- already past 10!

    REPEAT
        SELECT n AS body_ran_with;
        SET n = n + 1;
    UNTIL n > 10
    END REPEAT;
END$$

DELIMITER ;

CALL at_least_once();

Even though n > 10 was already true on entry, the body still ran once with n = 100. Then n incremented to 101, the condition was still true, and the loop exited. With WHILE, the body would never have executed.

A common use case for REPEAT: read input or process events until a sentinel condition. Here we simulate generating fake order numbers until we find one matching some criteria:

DROP PROCEDURE IF EXISTS find_lucky_seven;

DELIMITER $$

CREATE PROCEDURE find_lucky_seven(OUT attempts INT)
BEGIN
    DECLARE roll INT;
    SET attempts = 0;

    REPEAT
        SET attempts = attempts + 1;
        SET roll     = FLOOR(1 + RAND() * 10);    -- 1..10
    UNTIL roll = 7
    END REPEAT;
END$$

DELIMITER ;

CALL find_lucky_seven(@n);
SELECT @n AS attempts_to_roll_a_7;

(Your actual attempts count will vary because RAND() is random.) The body must run at least once to even generate a roll, so REPEAT's at-least-once semantics fit perfectly.

Like WHILE and LOOP, REPEAT can be exited early with LEAVE if the loop has a label:

my_repeat: REPEAT
    SET i = i + 1;
    IF some_error_condition THEN
        LEAVE my_repeat;
    END IF;
UNTIL i >= 100
END REPEAT my_repeat;

1. Reach for REPEAT when at-least-once is the natural pattern. "Do X, then keep doing it until Y" reads cleanly with REPEAT; forcing it into WHILE adds an awkward priming statement.
2. Remember the inverted condition. WHILE continues on true; REPEAT continues on false. Mistakes here are the #1 REPEAT bug.
3. Don't put a semicolon after UNTIL condition. The semicolon goes after END REPEAT, not the condition. This is the #2 REPEAT bug.
4. Always update the condition variable inside the body. Or you've got an infinite loop.
5. Label the loop if you might want to LEAVE or ITERATE from inside.

• REPEAT ... UNTIL cond END REPEAT; is a post-test loop — the body always runs at least once before the condition is checked.
• The loop continues while the condition is false and exits when it becomes true — the opposite polarity from WHILE.
• No semicolon between UNTIL condition and END REPEAT — it goes after END REPEAT.
• Best for "do once, then keep going until done" patterns and any loop where at-least-once execution makes natural sense.
• Like other loops, label it and use LEAVE for early exit.