In some cases MySQL auto_increment is far enough for generating sequence of values, for example in order to create unique IDs of records. But sometimes it is necessary to have full featured sequence with nextval() function just like in Oracle or PostgreSQL. Unfortunately MySQL doesn't have support for real sequences.
So, let's start with definition of the goal we would like to achieve. First, it is flexibility of having a sequence started from any number and incremented by value other than 1, may be even negative. Second, the option to make sequence value roll over to minimum value if it has reached the maximum. Finally, there must be a function like nextval() in Oracle or PostgreSQL to retrieve next possible value of the sequence.
First step to implement a sequence object with capabilities as mentioned above is to create table:
CREATE TABLE `sequence` ( `name` varchar(100) NOT NULL, `increment` int(11) NOT NULL DEFAULT 1, `min_value` int(11) NOT NULL DEFAULT 1, `max_value` bigint(20) NOT NULL DEFAULT 9223372036854775807, `cur_value` bigint(20) DEFAULT 1, `cycle` boolean NOT NULL DEFAULT FALSE, PRIMARY KEY (`name`) ) ENGINE=MyISAM;
Then we should insert some values to specify parameters of the sequence. The following example defines sequence counting down from 100 to 0 in cycle:
INSERT INTO sequence ( name, increment, min_value, max_value, cur_value ) VALUES ('my_sequence', -2, 0, 100, 100, 1);
Now it's a time to define nextval() function:
DELIMITER $$ CREATE FUNCTION `nextval` (`seq_name` varchar(100)) RETURNS bigint NOT DETERMINISTIC BEGIN DECLARE cur_val bigint; SELECT cur_value INTO cur_val FROM sequence WHERE name = seq_name; IF cur_val IS NOT NULL THEN UPDATE sequence SET cur_value = IF ( (cur_value + increment) > max_value OR (cur_value + increment) < min_value, IF ( cycle = TRUE, IF ( (cur_value + increment) > max_value, min_value, max_value ), NULL ), cur_value + increment ) WHERE name = seq_name; END IF; RETURN cur_val; END; $$
After all these steps done, you can extract values of the sequence as follows: