Loop n times without using a stored procedure

Question

How can I write a loop that runs n times in MySql without using a stored procedure.

This is how I do it with a stored procedure:

DELIMITER $$ DROP PROCEDURE IF EXISTS test$$ CREATE PROCEDURE test() BEGIN    DECLARE count INT DEFAULT 0;    WHILE count < 10 DO       /**Sql statement**/       SET count = count + 1;    END WHILE; END$$ DELIMITER ;   

And then I execute my procedure this way:

call test();   

If I remove the stored procedure and run the normal query, then it fails with this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE count INT DEFAULT 0; WHILE count < 10 DO at line' 2

I have looked through the Internet for a solution with no luck.

Edit Based On comments:

The above stored procedure does exactly what I want: It loops 10 times and execute my sql statement. Now I want to accomplish the same thing without using a stored procedure. Something like:

DECLARE count INT DEFAULT 0;    WHILE count < 10 DO       /**Sql statement**/       SET count = count + 1;    END WHILE;   

Answer 1

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.

Answer 2

You can do it direcly with MariaDB Sequence Engine. MariaDB is a binary replacement for MySQL.

"A Sequence engine allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment."

[Manual Sequence Engine]

Here are some Samples:

    mysql -uroot -p     Enter password: xxxxxxx     Welcome to the MariaDB monitor.  Commands end with ; or \g.     Your MariaDB connection id is 5     Server version: 10.0.20-MariaDB-log Homebrew      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.      MariaDB [(none)]> use tmp     Database changed     MariaDB [tmp]> select version();     +---------------------+     | version()           |     +---------------------+     | 10.0.20-MariaDB-log |     +---------------------+     1 row in set (0.00 sec)      MariaDB [tmp]> select * from seq_1_to_10;     +-----+     | seq |     +-----+     |   1 |     |   2 |     |   3 |     |   4 |     |   5 |     |   6 |     |   7 |     |   8 |     |   9 |     |  10 |     +-----+     10 rows in set (0.00 sec)      MariaDB [tmp]> select * from seq_1_to_10_step_2;     +-----+     | seq |     +-----+     |   1 |     |   3 |     |   5 |     |   7 |     |   9 |     +-----+     5 rows in set (0.00 sec)  MariaDB [tmp]> SELECT DAYNAME('1980-12-05' + INTERVAL (seq) YEAR) day,     ->     '1980-12-05' + INTERVAL (seq) YEAR date FROM seq_0_to_40; +-----------+------------+ | day       | date       | +-----------+------------+ | Friday    | 1980-12-05 | | Saturday  | 1981-12-05 | | Sunday    | 1982-12-05 | | Monday    | 1983-12-05 | | Wednesday | 1984-12-05 | | Thursday  | 1985-12-05 | | Friday    | 1986-12-05 | | Saturday  | 1987-12-05 | | Monday    | 1988-12-05 | | Tuesday   | 1989-12-05 | | Wednesday | 1990-12-05 | | Thursday  | 1991-12-05 | | Saturday  | 1992-12-05 | | Sunday    | 1993-12-05 | | Monday    | 1994-12-05 | | Tuesday   | 1995-12-05 | | Thursday  | 1996-12-05 | | Friday    | 1997-12-05 | | Saturday  | 1998-12-05 | | Sunday    | 1999-12-05 | | Tuesday   | 2000-12-05 | | Wednesday | 2001-12-05 | | Thursday  | 2002-12-05 | | Friday    | 2003-12-05 | | Sunday    | 2004-12-05 | | Monday    | 2005-12-05 | | Tuesday   | 2006-12-05 | | Wednesday | 2007-12-05 | | Friday    | 2008-12-05 | | Saturday  | 2009-12-05 | | Sunday    | 2010-12-05 | | Monday    | 2011-12-05 | | Wednesday | 2012-12-05 | | Thursday  | 2013-12-05 | | Friday    | 2014-12-05 | | Saturday  | 2015-12-05 | | Monday    | 2016-12-05 | | Tuesday   | 2017-12-05 | | Wednesday | 2018-12-05 | | Thursday  | 2019-12-05 | | Saturday  | 2020-12-05 | +-----------+------------+ 41 rows in set (0.00 sec)  MariaDB [tmp]> 

Here one Sample:

MariaDB [(none)]> use tmp Database changed MariaDB [tmp]> SELECT * FROM seq_1_to_5,     -> (SELECT * FROM animals) AS x     -> ORDER BY seq; +-----+------+-----------+-----------------+ | seq | id   | name      | specie          | +-----+------+-----------+-----------------+ |   1 |    1 | dougie    | dog-poodle      | |   1 |    6 | tweety    | bird-canary     | |   1 |    5 | spotty    | turtle-spotted  | |   1 |    4 | mr.turtle | turtle-snapping | |   1 |    3 | cadi      | cat-persian     | |   1 |    2 | bonzo     | dog-pitbull     | |   2 |    4 | mr.turtle | turtle-snapping | |   2 |    3 | cadi      | cat-persian     | |   2 |    2 | bonzo     | dog-pitbull     | |   2 |    1 | dougie    | dog-poodle      | |   2 |    6 | tweety    | bird-canary     | |   2 |    5 | spotty    | turtle-spotted  | |   3 |    6 | tweety    | bird-canary     | |   3 |    5 | spotty    | turtle-spotted  | |   3 |    4 | mr.turtle | turtle-snapping | |   3 |    3 | cadi      | cat-persian     | |   3 |    2 | bonzo     | dog-pitbull     | |   3 |    1 | dougie    | dog-poodle      | |   4 |    2 | bonzo     | dog-pitbull     | |   4 |    1 | dougie    | dog-poodle      | |   4 |    6 | tweety    | bird-canary     | |   4 |    5 | spotty    | turtle-spotted  | |   4 |    4 | mr.turtle | turtle-snapping | |   4 |    3 | cadi      | cat-persian     | |   5 |    5 | spotty    | turtle-spotted  | |   5 |    4 | mr.turtle | turtle-snapping | |   5 |    3 | cadi      | cat-persian     | |   5 |    2 | bonzo     | dog-pitbull     | |   5 |    1 | dougie    | dog-poodle      | |   5 |    6 | tweety    | bird-canary     | +-----+------+-----------+-----------------+ 30 rows in set (0.00 sec)  MariaDB [tmp]>