Can I run a loop in MySQL without using a procedure/function?

Question

For testing, is it possible to run a loop from MySQL workbench or similar tool? I tried but got an error.

If it is possible, please supply a simple example I can run.

Simply want to generate some test data e.g `for(i=0 to 100) insert ... values (i,'test'+i)` that sort of thing. I think Thanu's use of a temp proc is suitable. — Mr. Boy, Feb 07 '13 at 08:48

score 23 · Accepted Answer · edited Apr 13 '17 at 18:12

23

You can't do a for loop in an SQL editor without a stored procedure. I use TOAD for MySQL.

A quick stored procedure should do the job:

DELIMITER $$

DROP PROCEDURE IF EXISTS proc_loop_test$$
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val; 
  END LOOP; 
END$$

DELIMITER ;

edited Apr 13 '17 at 18:12

informatik01

16,038
10
74
104

answered Feb 06 '13 at 23:05

Thanu

2,481
8
34
53

2

OK. After over an hour of testing, I ended up with: "_At `CREATE PROCEDURE proc_loop_test() BEGIN DECLARE int_val INT DEFAULT 0;` MySQL said: `#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 '' at line 3`_". What am I missing? – trejder Apr 02 '15 at 13:37
Sorry, I cant say whats really wrong without seeing your exact full proc code. Because it can be just a missing semicolon. – Thanu Apr 03 '15 at 01:47
6

had the same problem. Resolved it by changing the delimiter before the procedure to `$$` and changing it back to `;` afterwards. More info: http://www.mysqltutorial.org/stored-procedures-loop.aspx – Chris Apr 06 '16 at 13:02

Kache · Answer 2 · 2014-11-12T21:39:07.690

There's a trick with limited use-cases that is "loop-like".

I wanted to create a large (1~2 million) row table of random integers for a test:

INSERT INTO test_table (num) VALUES(ROUND(RAND() * 1E6));

-- calling this will insert once for every row in test_table
INSERT INTO test_table (num)
SELECT ROUND(RAND() * 1E6)
FROM test_table;

So I quickly just kept doubling the number of rows until I had what I needed.

score 1 · Answer 3 · answered Apr 16 '18 at 12:07

1

Supposed that you already have an arbitrary table myOldTable which is sufficiently long you could use the following trick:

set @counter = 0;
select (@counter := @counter+1), @counter*@counter from myOldTable limit 1000;

answered Apr 16 '18 at 12:07

Volker Raab

11
1

score 0 · Answer 4 · answered Feb 07 '13 at 07:39

0

If it is that you only want to block the current thread then use select sleep(seconds); otherwise you can use a stored procedure (if there's something you want to loop over) or a UDF (user defined function).

answered Feb 07 '13 at 07:39

Mike Lischke

48,925
16
119
181

Can I run a loop in MySQL without using a procedure/function?

4 Answers4

Linked