Generating a range of numbers in MySQL?

To generate a range of numbers in MySQL, you can use stored procedure. Firstly, we need to create a table. After that, we will create a stored procedure that generates a range of number from 10 to 1.

The following is the query to create a table −

mysql> create table GeneratingNumbersDemo
   −> (
      −> Number int
   −> );
Query OK, 0 rows affected (0.55 sec)

Now you can create a stored procedure that stores a range of numbers in the table. The following is the query to create a stored procedure −

mysql> delimiter //
mysql> CREATE PROCEDURE Stored_ProceduretoGenerateNumbersDemo()
   −> BEGIN
   −> DECLARE start INT DEFAULT 10;
   −> WHILE start > 0 DO
   −> INSERT GeneratingNumbersDemo VALUES (start);
   −> SET start = start - 1;
   −> END WHILE;
   −> END //
Query OK, 0 rows affected (0.12 sec)

After that we need to call the stored procedure that fills a range of numbers in the table.

You can call the stored procedure with the help of call command. The syntax is as follows −

call yourStoredProcedureName();

Now you can call above stored procedure like this −

Calling the stored procedure −

mysql> call Stored_ProceduretoGenerateNumbersDemo();
Query OK, 1 row affected (0.85 sec)

Check whether the range of numbers are present in the table or not. The query is as follows −

mysql> select *from GeneratingNumbersDemo;

The following is the output −

+--------+
| Number |
+--------+
|     10 |
|      9 |
|      8 |
|      7 |
|      6 |
|      5 |
|      4 |
|      3 |
|      2 |
|      1 |
+--------+
10 rows in set (0.00 sec)