«

»

Dec 26

MySQL Number Sequence Generator

So we know that we can use the following to generate a sequence of numbers during a SELECT from a table

SET @numGen = 0;

SELECT [fields], @numGen := @numGen + 1

but this works because our variable’s value is being altered for every row we get from our SELECT so what do we do when we have when we want a sequence but no table to do a select from? we can’t do

SELECT @numGen := @numGen + 1
LIMIT 100;

Because LIMIT only prevents SELECT returning more rows that what we have specified and the code above will only return one row.

Our solution is to do a loop and bellow is a Stored Procedure you can create and call.

DELIMITER $$

/*
Name:			GenSequence
Input:			numLimit	<Argument 1>	[Integer]
				increment	<Argument 2>	[Integer]
				selectedTable	<Argument 3>	[Varchar]
				selectField	<Argument 4>	[Varchar]
Output:         N/A
Description:
Generates a sequence of numbers that get stored in a table.
*/
CREATE PROCEDURE GenSequence(IN numLimit INT, increment INT, selectedTable VARCHAR(255), selectField VARCHAR(255))
BEGIN
	DECLARE pointer INT DEFAULT increment;
	WHILE pointer <= numLimit DO
		SET @execS = CONCAT("INSERT `",selectedTable,"` (`",selectField,"`) VALUES (",pointer,");");
		PREPARE stmt1 FROM @execS; 
		EXECUTE stmt1; 
		DEALLOCATE PREPARE stmt1; 
		SET pointer = pointer + increment;
	END WHILE;
END$$
 
DELIMITER ;

CALL GenSequence(100, 5, "myTable", "id");

Simply put this Stored Procedure will use a loop to repeatedly insert into a table increasing the value of the number we are inserting. for convenience i have made it use a Prepared Statement so i can actually generate the query which contains out Table and Field

Where would you use this? well say you wanted to generate a list of IDs with a different increment amount for a table which already had an Auto Increment set or if you just wanted a list of numbers to demonstrate something for a Stack Overflow question/answer. there’s probably other uses for this sort of code which i havn’t came across

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>