Feb 17, 2020 8:25 AM

Using WHILE loop in MySQL

Using WHILE loop in MySQL

In this article I will explain how to use the WHILE loop in MySQL. Recently, I was faced with the task of generating sequence numbers in MySQL (similar to the AUTO_INCREMENT attribute). Unfortunately, there is no function in MySQL for generating sequence numbers. But we can use the WHILE loop statement to execute a block of code multiple times when the condition is met.

For example, let's create a simple product table:

--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
	`id` INT UNSIGNED NOT NULL,
	`code` VARCHAR(64) DEFAULT NULL
);

--
-- Dumping data for table `invoices`
--
INSERT INTO `products` (`id`) VALUES (1000), (1001), (1002), (1003), (1004), (1005), (1006), (1007), (1008), (1009), (1010);

Suppose we need to add unique names containing sequence numbers in the `code` field. Let's create a generateCodes stored procedure that updates the `code` field of the `products` table:

DROP PROCEDURE IF EXISTS generateCodes;

DELIMITER $$
CREATE PROCEDURE generateCodes(startId INT, endId INT, codePrefix VARCHAR(64))
BEGIN
	SET @i = IFNULL(startId, 1);
	SET @n = 1;

	WHILE @i <= endId DO
		UPDATE `products` SET `code` = CONCAT(codePrefix, '_', @n) WHERE `id` = @i;
		SET @i = @i + 1;
		SET @n = @n + 1;
	END WHILE;
END $$
DELIMITER ;

Unfortunately, you cannot create a stored procedure with a check for existence:

CREATE PROCEDURE IF NOT EXISTS generateCodes(startId INT, endId INT, codePrefix VARCHAR(64))

Therefore, at the beginning of creating a stored procedure, we delete it so that the error "PROCEDURE generateCodes already exists" does not occur.

Now we can call the stored procedure generateCodes to add unique names containing sequence numbers:

CALL generateCodes(1000, 1004, 'first_category');
CALL generateCodes(1005, 1010, 'second_category');

We will output the result by running the query:

SELECT * FROM `products`;

--
--	[output]
--
--	id		code
--	1000	first_category_1
--	1001	first_category_2
--	1002	first_category_3
--	1003	first_category_4
--	1004	first_category_5
--	1005	second_category_1
--	1006	second_category_2
--	1007	second_category_3
--	1008	second_category_4
--	1009	second_category_5
--	1010	second_category_6

Other articles