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