Home > Documentation > MySQL

Emulating Sequences in MySQL

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:

SELECT nextval('my_sequence');

Have questions? Contact us