Introduction

MySQL does not have a native FOR loop in the same way many procedural languages do. In stored programs, you usually express the same idea with WHILE, LOOP, or REPEAT, and in many cases the better answer is to avoid looping entirely and write a set-based query.

Use WHILE as the Closest Counter-Loop Equivalent

If you want the procedural shape of a classic counter loop, WHILE is usually the clearest choice.

1DELIMITER //
2
3CREATE PROCEDURE demo_while()
4BEGIN
5    DECLARE i INT DEFAULT 1;
6
7    WHILE i <= 5 DO
8        INSERT INTO numbers_log(value_col) VALUES (i);
9        SET i = i + 1;
10    END WHILE;
11END //
12
13DELIMITER ;

This is the most natural translation of "start at 1, keep going while the condition holds, increment each iteration."

Use LOOP When Exit Logic Is More Manual

LOOP gives you a lower-level structure with an explicit exit.

1DELIMITER //
2
3CREATE PROCEDURE demo_loop()
4BEGIN
5    DECLARE i INT DEFAULT 1;
6
7    my_loop: LOOP
8        IF i > 5 THEN
9            LEAVE my_loop;
10        END IF;
11
12        INSERT INTO numbers_log(value_col) VALUES (i * 10);
13        SET i = i + 1;
14    END LOOP;
15END //
16
17DELIMITER ;

This form is useful when the stop condition is more complicated than a simple counter check.

Use REPEAT for Do-While Behavior

REPEAT evaluates the stop condition at the end, which means the loop body runs at least once.

1DELIMITER //
2
3CREATE PROCEDURE demo_repeat()
4BEGIN
5    DECLARE i INT DEFAULT 1;
6
7    REPEAT
8        INSERT INTO numbers_log(value_col) VALUES (i * 100);
9        SET i = i + 1;
10    UNTIL i > 3
11    END REPEAT;
12END //
13
14DELIMITER ;

This is the MySQL equivalent of a procedural do-while pattern.

Prefer Set-Based SQL When Possible

A lot of SQL loops are a sign that the work could be expressed more efficiently as a set-based operation. For example, generating a short numeric sequence can often be done without procedural iteration.

1WITH RECURSIVE seq AS (
2    SELECT 1 AS n
3    UNION ALL
4    SELECT n + 1 FROM seq WHERE n < 5
5)
6SELECT n FROM seq;

Set-based SQL is usually easier for the optimizer to handle and easier for other SQL developers to reason about. Use procedural loops only when the task is genuinely stateful or row-by-row in nature.

Cursor Loops Are a Different Case

When each row from a query needs procedural handling, MySQL uses a cursor loop rather than a direct FOR row IN ... syntax.

1DECLARE done INT DEFAULT 0;
2DECLARE v_id INT;
3DECLARE cur CURSOR FOR SELECT id FROM source_table;
4DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

That style is more verbose than many procedural languages, which is another reason to stay set-based unless iteration is truly necessary.

Add Safety Guards

Whenever you write a procedural loop in MySQL, think about runaway behavior. User-driven loop counts, missing counter updates, or bad exit conditions can create unexpectedly expensive procedures.

Guardrails such as maximum iteration limits or input validation help prevent that.

IF input_n > 10000 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'input too large';
END IF;

These checks matter more in SQL procedures than many developers expect because the loop runs inside the database engine itself.

Common Pitfalls

Expecting a direct FOR syntax in MySQL stored procedures is a common misunderstanding.

Using loops for work that should be expressed as set-based SQL often makes the procedure slower and harder to maintain.

Forgetting the counter update or exit condition leads to infinite loops surprisingly easily.

Summary

• MySQL typically uses WHILE, LOOP, and REPEAT instead of a native FOR loop.
• 'WHILE is the closest match to a normal counter loop.'
• 'LOOP and REPEAT are useful for more specific control-flow shapes.'
• Prefer set-based SQL whenever the logic does not truly require procedural iteration.