How to Use the REPEAT Statement in MySQL

08/06/2021

Contents

In this article, you will learn how to use the REPEAT statement in MySQL.

Using the REPEAT statement in MySQL

The REPEAT statement in MySQL is used to repeat a set of SQL statements a specified number of times. It is a loop statement that executes the same block of SQL code repeatedly until a certain condition is met.

Syntax

The basic syntax of the REPEAT statement in MySQL is as follows:

REPEAT
    statements;
UNTIL condition;

The statements block contains the SQL statements that will be executed repeatedly until the condition is met. The condition is checked after each iteration of the loop. If the condition is true, the loop will stop executing, and if it is false, the loop will continue executing.

Examples

Repeat statement with a counter

In this example, we will use the REPEAT statement to print the numbers from 1 to 10.

SET @counter = 1;

REPEAT
    SELECT @counter;
    SET @counter = @counter + 1;
UNTIL @counter > 10;

or

1
2
3
4
5
6
7
8
9
10

The SET statement initializes the @counter variable to 1. Then, we use the REPEAT statement to print the value of @counter and increment it by 1 until @counter is greater than 10. The loop continues executing until the condition is met.

Repeat statement with a condition

In this example, we will use the REPEAT statement to find the factorial of a number.

SET @number = 5;
SET @factorial = 1;

REPEAT
    SET @factorial = @factorial * @number;
    SET @number = @number - 1;
UNTIL @number = 0;

SELECT @factorial;

or

120

The SET statements initialize the @number and @factorial variables to 5 and 1, respectively. Then, we use the REPEAT statement to calculate the factorial of @number and decrement it by 1 until @number is equal to 0. The loop continues executing until the condition is met. Finally, we select the value of @factorial.

Repeat statement with BREAK keyword

In this example, we will use the REPEAT statement to print the even numbers from 2 to 10.

SET @counter = 2;

REPEAT
    SELECT @counter;
    SET @counter = @counter + 2;
    IF @counter > 10 THEN
        BREAK;
    END IF;
UNTIL 1 = 0;

or

2
4
6
8
10

The SET statement initializes the @counter variable to 2. Then, we use the REPEAT statement to print the value of @counter and increment it by 2. We use the IF statement to check if @counter is greater than 10, and if it is, we use the BREAK keyword to exit the loop. The loop continues executing until the condition is met.