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:
- 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.
- 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):
- When the sequence doesn’t exist
- 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.
Excellent note! very usefull!!
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.
Thank you Gopi. Glad it’s useful for you.
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;
[…]
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…
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.
whoa! this is an excellent alternative. however, I wish mysql will create a similar function.
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’);
@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.
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 ;
@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.
Hi,
please advice.
if i have connection with auto commit false,
then this function when will commit the operation.
Thanks
@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;
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 ?
Great post! Thanks
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
Thanks!
Very useful solution
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)
Hi there !
I have the same error as Jagpreet Singh. Have you fixed it ? Please help
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.
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.
Nice article. thanks
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!!!!