Does mysql have a for loop?

Mysql does not have a for loop. MySQL does not support for loop statements. It supports three loop statements: WHILE, REPEAT and LOOP. The WHILE loop checks the expression at the beginning of each iteration; the REPEAT loop statement, also known as the post-test loop, checks the expression after executing the statement; the LOOP statement can repeatedly execute a block of code.

The operating environment of this tutorial: windows10 system, mysql version 8.0, Dell G3 computer.

Loop statements in mysql

MySQL provides loop statements that allow you to repeatedly execute a block of SQL code based on conditions.

MySQL does not support for loop statements. MySQL only supports while loops, repeat loops, and loop loops.

MySQL provides loop statements that allow you to repeatedly execute a block of SQL code based on conditions. MySQL does not support for loop statements. There are three loop statements in MySQL: WHILE, REPEAT and LOOP.

We will examine each loop statement in more detail in the following sections.

WHILE loop

WHILEThe syntax of the statement is as follows:

WHILE expression DO
   statements
END WHILE

WHILEThe loop checks the expression at the beginning of each iteration. If expressionevaluates is TRUE, MySQL will execute the statements between WHILE and END WHILE until expressionevaluates is FALSE. WHILEThe loop is called a pretest conditional loop because it always checks the expression of a statement before executing it.

The flow chart below illustrates the WHILE loop statement:

The following is used in a stored procedure WHILEExample of loop statement:

DELIMITER $$
 DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
 CREATE PROCEDURE test_mysql_while_loop()
 BEGIN
 DECLARE x  INT;
 DECLARE str  VARCHAR(255);

 SET x = 1;
 SET str =  '';

 WHILE x  <p>In the above <code>test_mysql_while_loop</code> stored procedure: </p>

• First, repeatedly build the str string, Until the value of the x variable is greater than 5.
• Then, use the SELECT statement to display the final string.

It should be noted that , if the value of the x variable is not initialized, its default value is NULL. Therefore, the condition in the WHILE loop statement will always be TRUE, and you will have an indeterminate loop, which is unpredictable.

Let’s testtest_mysql_while_loopstoredCall the stored procedure:

CALL test_mysql_while_loop();

Execute the above query statement and get the following results-

mysql> CALL test_mysql_while_loop();
+------------+
| str        |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set

Query OK, 0 rows affected

REPEAT Loop

REPEATThe syntax of a loop statement is as follows:

REPEAT
 statements;
UNTIL expression
END REPEAT

First, MySQL executes the statement and then evaluates the evaluation expression (expression ). If an expression (expression) evaluates to FALSE, MySQL will execute the statement repeatedly until the expression evaluates to TRUE.

Because the REPEAT loop statement checks the expression (expression) after executing the statement, the REPEAT loop statement is also called a post-test loop.

The following flow chart illustrates the execution process of the REPEAT loop statement:

We can use REPEAT The loop statement rewrites the test_mysql_while_loop stored procedure, using the WHILE loop statement:

DELIMITER $$
 DROP PROCEDURE IF EXISTS mysql_test_repeat_loop$$
 CREATE PROCEDURE mysql_test_repeat_loop()
 BEGIN
 DECLARE x INT;
 DECLARE str VARCHAR(255);

 SET x = 1;
        SET str =  '';

 REPEAT
 SET  str = CONCAT(str,x,',');
 SET  x = x + 1; 
        UNTIL x  > 5
        END REPEAT;

        SELECT str;
 END$$
DELIMITER ;

It should be noted that there is no semicolon in the UNTIL expression ( ;).

Execute the above query statement and get the following results-

mysql> CALL mysql_test_repeat_loop();
+------------+
| str        |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set

Query OK, 0 rows affected

LOOP, LEAVE and ITERATE statements

There are two statements Allows you to use to control loops:

• LEAVE statement is used to exit the loop immediately without waiting to check the condition. The working principle of the LEAVE statement is similar to the break statement in PHP, C/C , Java and other languages. The
• ITERATE statement allows you to skip the entire remaining code and start a new iteration. The ITERATE statement is similar to the continue statement in PHP, C/C , Java, etc.

MySQL also has a LOOP statement, which can execute a block of code repeatedly, plus the flexibility of using loop labels.

The following is an example of using the LOOP loop statement.

CREATE PROCEDURE test_mysql_loop()
 BEGIN
 DECLARE x  INT;
        DECLARE str  VARCHAR(255);

 SET x = 1;
        SET str =  '';

 loop_label:  LOOP
 IF  x > 10 THEN 
 LEAVE  loop_label;
 END  IF;

 SET  x = x + 1;
 IF (x mod 2) THEN
     ITERATE  loop_label;
 ELSE
    SET  str = CONCAT(str,x,',');
 END IF;
    END LOOP;    
    SELECT str;
END;

• 以上存储过程仅构造具有偶数字符串的字符串，例如2,4,6等。
• 在LOOP语句之前放置一个loop_label循环标签。
• 如果x的值大于10，则由于LEAVE语句，循环被终止。
• 如果x的值是一个奇数，ITERATE语句忽略它下面的所有内容，并开始一个新的迭代。
• 如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串。

执行上面查询语句，得到以下结果 -

mysql> CALL test_mysql_loop();
+-------------+
| str         |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set

Query OK, 0 rows affected

【相关推荐：mysql视频教程】

The above is the detailed content of Does mysql have a for loop?. For more information, please follow other related articles on the PHP Chinese website!