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;
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