Bookmark and Share

Emulating nextval() function to get sequence in MySQL

Posted: Sunday, February 8th, 2009 at 3:08 pmUpdated: Sunday, February 28th, 2016 at 11:38 pm

There are times when auto increment is not sufficient. And there are times when what you need is actually a sequence. Unfortunately MySQL doesn’t have support for sequence. Having been working on PostgreSQL database as well, I found that it’s nextval() function is pretty nice. So in this article, I’d like to emulate (or implement) PostgreSQL’s nextval equivalent in MySQL.

What are we working towards

In PostgreSQL, you need to first define a sequence then call it using nextval() function. The goal of this article is to implement nextval() function in MySQL to behave as closely as possible to PostgreSQL’s nextval(). In PostgreSQL, here’s how you would call nextval().

-- Run this statement on PostgreSQL server and substitute
-- with your actual sequence name
SELECT nextval('sequence_name');

If you run the above statement in MySQL, you’d get error code 1305, function nextval doesn’t exist. The goal is to have it working exactly like how it is in PostgreSQL.

Database structure.

Here’s my database structure to accomodate nextval. Depending on your database setup, it may be a good idea to have the sequence related stuff on its own database. This setup, off course, works if you have dedicated database server for you. Otherwise, you can simply create the table and function within your database.

CREATE DATABASE `sequence`;
CREATE TABLE `sequence`.`sequence_data` (
	`sequence_name` varchar(100) NOT NULL,
	`sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
	`sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
	`sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
	`sequence_cur_value` bigint(20) unsigned DEFAULT 1,
	`sequence_cycle` boolean NOT NULL DEFAULT FALSE,
	PRIMARY KEY (`sequence_name`)
) ENGINE=MyISAM;

A little explanation on the table columns.

My definition of a sequence is a positive number that grows bigger. Thus, all the columns are set for unsigned. If you need a sequence that can go negative, take out the unsigned attribute.

I also want flexibility of having a sequence incremented by other than 1. So, for example, if I want to have a sequence with only even values or odd values, I can simply start at an even number or odd number and have sequence_increment set to 2.

The last column (sequence_cycle) is a toggle for if a sequence value can roll over to minimum value if it has reached the maximum value. I also made sequence_cur_value null-able because I want to have the column value as NULL when the sequence has overflow and sequence cycle is set to false (to detect error condition).

Creating sequence

Creating a sequence, therefore, is as simple as inserting a row in sequence table.

-- This code will create sequence with default values.
INSERT INTO sequence.sequence_data
	(sequence_name)
VALUE
	('sq_my_sequence')
;

-- You can also customize the sequence behavior.
INSERT INTO sequence.sequence_data
	(sequence_name, sequence_increment, sequence_max_value)
VALUE
	('sq_sequence_2', 10, 100)
;

Defining nextval() in MySQL

Now that we got the data structure down and created a few sequences, let’s take a look at my definition for nextval() function in MySQL.

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
	DECLARE cur_val bigint(20);

	SELECT
		sequence_cur_value INTO cur_val
	FROM
		sequence.sequence_data
	WHERE
		sequence_name = seq_name
	;

	IF cur_val IS NOT NULL THEN
		UPDATE
			sequence.sequence_data
		SET
			sequence_cur_value = IF (
				(sequence_cur_value + sequence_increment) > sequence_max_value,
				IF (
					sequence_cycle = TRUE,
					sequence_min_value,
					NULL
				),
				sequence_cur_value + sequence_increment
			)
		WHERE
			sequence_name = seq_name
		;
	END IF;

	RETURN cur_val;
END$$

If you are using SQLyog, the best way is to right click on Functions and click on create function or click on Objects -> Functions -> Create Function… then modify it to match the function definition above.

Breaking down nextval() function

Let’s see what I did on the function. I declare the function non deterministic because calling it with the same argument doesn’t guarantee the same value. As a matter of fact, it should never return the same value except when sequence_cycle is set.

After declaring variable, we select current sequence value from the table, putting the result to cur_val variable (line 6 – 12). Then, if cur_val is not null (remember from my explanation why I have sequence_cur_value column null-able), we want to increment the cur_val so that next time this function is called, the next sequence is ready to be fetched.

Updating sequence_cur_val is rather tricky. We have to consider:

  1. If the next sequence is still within the limit as specified in sequence_max_value, we’ll simply increment sequence_cur_val by value specified in sequence_increment.
  2. If the next sequence is outside the boundary, we need to check
    • If sequence_cycle is set, reset sequence_cur_val to sequence_min_val.
    • If sequence_cycle is not set, assign NULL as sequence_cur_val to identify overflow / error condition.

Then finally, we return cur_val. Note that the select on line 6 – 12 will return null if the sequence name doesn’t exist. Thus, there’s only 2 cases when nextval() returns NULL (and both are error condition):

  1. When the sequence doesn’t exist
  2. When current sequence value out of range

How to call nextval() function in MySQL

Now that we’ve defined everything and the function is made, we can simply call nextval() as follows. Adjusts the call depending on whether you’re working on a separate database or not as discussed earlier.

SELECT nextval('sq_my_sequence') as next_sequence;

There you have it. There’s a catch with this function that I’ve discussed in my next article Keep replication in mind when writing SQL code.. I hope this article helps you. As always, I welcome comments / questions / critics that will help me and other readers understand better.

23 Responses to “Emulating nextval() function to get sequence in MySQL”

  1. nithya Says:

    Excellent note! very usefull!!

  2. Gopi Srinivas Says:

    This article is pretty good and was very helpful in creating sequence implementation in MySQL. We have done the implementation in one of our application.

  3. Maresa Says:

    Thank you Gopi. Glad it’s useful for you.

  4. Luiz Weber TI Says:

    Great job!!!

    I just added a few lines before closing the IF to enter the sequence automatically failing:

    […]
    WHERE
    sequence_name = seq_name
    ;
    INSERT INTO
    sequence.sequence_data
    SET
    sequence_cur_value = 0,
    sequence_name = seq_name;
    END IF;
    […]

  5. nonymous Says:

    How do guarantee – once you get 100 clients beating on your database – that you won’t get the same sequence ID returned to multiple clients?
    If your mysql server is running on a multi-processor system, you may VERY WELL have the nextval function being ran twice simultaneously. Two clients will read the SAME value for sequence_cur_value…

  6. Maresa Says:

    Good question. I vaguely remember testing concurrency and found out that if you have 2 concurrent statements, the second one is in blocked state until the first one finished executing. I tested it by adding sleep() function and call nextval() from 2 different connections.

    However, to be even more safe, I guess I can change the sequence table to use InnoDB and do SELECT FOR UPDATE on the stored procedure. Off course it’ll be within transaction.

  7. kwatog Says:

    whoa! this is an excellent alternative. however, I wish mysql will create a similar function.

  8. okeefedp Says:

    I did it like this:

    A table to represent each sequence: (In this example, a batch ID sequence)

    DROP TABLE IF EXISTS BatchIDSequence;

    CREATE TABLE BatchIDSequence (
    ID int(11) NOT NULL AUTO_INCREMENT
    ,processID int(11) NOT NULL
    ,PRIMARY KEY (ID)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    A NextVal stored procedure to that uses a prepared statement and accepts the sequence name as the parameter.

    DROP PROCEDURE IF EXISTS pNextVal;
    CREATE PROCEDURE pNextVal(in_sequenceName VARCHAR(60))
    BEGIN
    SET @stmt_text=CONCAT(“INSERT INTO “, in_sequenceName, “Sequence (processID) VALUES(?)”);
    PREPARE stmt FROM @stmt_text;
    SET @a=”1″;
    EXECUTE stmt USING @a;
    SELECT LAST_INSERT_ID();
    END;

    It would be called like this:

    call pNextVal(‘BatchID’);

  9. Maresa Says:

    @okeefedp : I like the alternative way to doing this. :-) However, if you want to have sequence to increase by other than 1, or if you want a sequence max value of some number, it’s not possible.

    Another thing is that using one table for each sequence, plus if your sequence is large (for example the current sequence is 1M), then you essentially have a table with 1M rows. That would be overkill, I think.

  10. CesarDraw Says:

    congrats! I corrected some lines that were causing errors and thus worked:

    DELIMITER $$

    DROP FUNCTION IF EXISTS `fac201001`.`nextval` $$
    CREATE FUNCTION `sequence`.`nextval` (`seq_name` varchar(100)) RETURNS bigint(20) NOT DETERMINISTIC
    BEGIN
    DECLARE cur_val bigint(20);

    SELECT
    sequence_cur_value INTO cur_val
    FROM
    `sequence`.`sequence_data`
    WHERE
    sequence_name = seq_name;

    IF cur_val IS NOT NULL THEN
    UPDATE
    `sequence`.`sequence_data`
    SET
    sequence_cur_value = IF (
    (sequence_cur_value + sequence_increment) > sequence_max_value,
    IF (
    sequence_cycle = TRUE,
    sequence_min_value,
    NULL
    ),
    sequence_cur_value + sequence_increment
    )
    WHERE
    sequence_name = seq_name;
    END IF;

    RETURN cur_val;

    END $$

    DELIMITER ;

  11. Maresa Says:

    @CaesarDraw Thank you. Mind letting me know what lines wasn’t working? I see one that I didn’t realize that somehow grater than ( > ) is being printed as >. It is fixed.

  12. Vishal Says:

    Hi,

    please advice.
    if i have connection with auto commit false,
    then this function when will commit the operation.

    Thanks

  13. Maresa Says:

    @Vishal: I’m trying to guess what you mean.

    START TRANSACTION;
    SELECT @myid := nextval(‘sq_my_sequence’) as next_sequence;
    INSERT INTO some_table (id, name) VALUES (@myid, ‘My Name’);
    COMMIT;

  14. Vishal Says:

    Thanks Maresa,
    But i was thinking writing function in DB, which will take care all commit operation as well (i.e. inside Function – nextval). So user of function would not bother about any commit operation.

    But here in function we cannot fire commit as – it is not allowed in mysql (explicit implicit commit).
    So i was wondering any other way to do same ?

  15. Zeljko Says:

    Great post! Thanks

  16. Jan Says:

    Thanks for the post, very clever idea. For the latecomers it would be nice if you could update your original post with the code from CesarDraw. Your original function code does not work as is.

    Best regards,
    Jan

  17. Rajesh Says:

    Thanks!
    Very useful solution

  18. Jagpreet Singh Says:

    Great help!! But i could not succeed. I am facing following problem –
    Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

  19. christophe Says:

    Hi there !
    I have the same error as Jagpreet Singh. Have you fixed it ? Please help

  20. Lena Says:

    SQL can execute qeuires against a database, retrieve,update,insert data from a database and empty refer to an empty result of no rows and NULL refer to a table with one row holding a NULL value.

  21. Aboup lah Says:

    Thanks for this article,
    It help me and save me a lot of time.
    Please, Is it possible to reset automaticaly the value of \\\’sequence_cur_value\\\’ at the end of a period, for example at the end of the month or year ?
    Any body help me, please?
    Thanks in avance.

  22. Manoj Says:

    Nice article. thanks

  23. corine Says:

    hello, i had an error when creating my nextval function:
    Erreur

    Requête SQL:

    CREATE FUNCTION `nextval` (
    seq_name varchar( 100 )
    ) RETURNS bigint( 20 ) NOT DETERMINISTIC BEGIN DECLARE cur_val bigint( 20 ) ;

    MySQL a répondu: Documentation
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 3

    please help me!!!!

Leave a Reply