MariaDB For Loop
I am trying to create 100 empty rows using a for loop, the insert statement by itself works ok but the for loop fails with a syntax error BEGIN FOR i IN 1..100 DO INSERT INTO tbladdress ( line1 ) VALUES (''); END FOR; END;
Answer Answered by Anel Husakovic in this comment.
well, you didn't specify which version you are using.
Looking from documentation for FOR loop,
FOR loop is introduced in
10.3, so if you are using it you can safely say:
CREATE TABLE t1 (line CHAR(3)); DELIMITER // FOR i IN 1..100 DO INSERT INTO t1 VALUES (""); END FOR; // DELIMITER ; SELECT * FROM t1;
But if you want you can create a procedure and use
DELETE FROM t1; DELIMITER // CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; INSERT INTO t1 VALUES (""); UNTIL @x >= p1 END REPEAT; END // CALL dorepeat(100)// SELECT @x// DELIMITER ; SELECT * FROM t1; DROP TABLE t1;
In 10.2 we are obtaining like so:
MariaDB [test]> SELECT @x// +------+ | @x | +------+ | 100 | +------+ 1 row in set (0.001 sec)
and in 10.3 we are obtaining bug (one more iteration, will investigate more in MDEV-19905), like so:
MariaDB [test]> SELECT @x// +------+ | @x | +------+ | 101 | +------+ 1 row in set (0.000 sec)
Workaround in this case is to use
99 calls of