Master MySQL WHILE Loop in Minutes

1 year ago
Ava Mitchell
2 minutes

You can use the WHILE loop statement in MySQL to repeatedly execute a block of code until a specified condition is no longer met.

The basic syntax of a WHILE loop is as follows:

WHILE condition DO
   statements;
END WHILE;

In this case, the condition is a logical expression, and the statements are the code blocks that need to be repeatedly executed.

Here is an example using a WHILE loop statement, inserting numbers 1 to 10 into a table named numbers:

DELIMITER //

CREATE PROCEDURE insert_numbers()
BEGIN
   DECLARE i INT DEFAULT 1;
   
   WHILE i <= 10 DO
      INSERT INTO numbers (number) VALUES (i);
      SET i = i + 1;
   END WHILE;
   
   SELECT * FROM numbers;
END //

DELIMITER ;

CALL insert_numbers();

In this example, a stored procedure insert_numbers() is created first, in which a variable i is declared and initialized to 1. Then, a WHILE loop is used to insert numbers from 1 to 10 into the numbers table, gradually increasing the value of i. Finally, the data in the numbers table is displayed using a SELECT statement.

It is important to note that when using a WHILE loop, the statements need to be enclosed between BEGIN and END, and a custom delimiter needs to be specified with the DELIMITER statement to avoid conflicts between the semicolons in the statements and the semicolon of the WHILE loop.

bannerAds