Loop n times without using a stored procedure

Question

MySQL docs on Flow Control Statements say:

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT
constructs for flow control within stored programs.

Docs on Stored Programs and Views say:

Stored program definitions include a body that may use compound
statements, loops, conditionals, and declared variables.

Compound-Statement Syntax

This section describes the syntax for the BEGIN … END compound
statement and other statements that can be used in the body of stored
programs: Stored procedures and functions, triggers, and events.

A compound statement is a block that can contain other blocks;
declarations for variables, condition handlers, and cursors; and flow
control constructs such as loops and conditional tests.

So, it looks like you can run an explicit loop only within a stored procedure, function or trigger.

Depending on what you do in your SQL statement, it may be acceptable to use a table (or view) of numbers (Creating a “Numbers Table” in mysql, MYSQL: Sequential Number Table).

If your query is a SELECT and it is OK to return result of your SELECT 10 times as one long result set (as opposed to 10 separate result sets) you can do something like this:

SELECT MainQuery.*
FROM
    (
        SELECT 1 AS Number 
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9
        UNION ALL SELECT 10
    ) AS Numbers
    CROSS JOIN
    (
        SELECT 'some data' AS Result
    ) AS MainQuery

Example for INSERT

I recommend to have a permanent table of numbers in your database. It is useful in many cases. See the links above how to generate it.

So, if you have a table Numbers with int column Number with values from 1 to, say, 100K (as I do), and primary key on this column, then instead of this loop:

DECLARE count INT DEFAULT 0;
WHILE count < 10 DO
    INSERT INTO table_name(col1,col2,col3) 
    VALUES("val1","val2",count);

    SET count = count + 1;
END WHILE;

you can write:

INSERT INTO table_name(col1,col2,col3)
SELECT ("val1", "val2", Numbers.Number-1)
FROM Numbers
WHERE Numbers.Number <= 10;

It would also work almost 10 times faster.

Leave a Comment Cancel reply