The Ultimate Guide to High Availability with MariaDB

MariaDB For Loop

Hi

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.

Hi,

well, you didn't specify which version you are using.

Looking from documentation for FOR loop, seems like 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 REPEAT loop, like here:

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 dorepeat.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.