Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public benefit or my later use
create function seq(seq_name char (20)) returns int
update seq set val=last_insert_id(val+1) where name=seq_name;
CREATE TABLE `seq` (
`name` varchar(20) NOT NULL,
`val` int(10) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into seq values('one',100);
insert into seq values('two',1000);
This implementation uses single table to maintain multiple sequences which application can use in desired way, though you of course could have them using different tables.
I use MyISAM tables here which allows to use such sequences in transactions without serializing transactions which require access to same sequence, though it is not as safe as if you use Innodb table in this case.
Even though implementation is just 2 lines of code it seems to confuse a lot of people because last_insert_id() is used rather unusual way – with argument. This way of using this function allows you to “inject” the value to be returned next time this function is called.
Sometimes people wonder why you would like to use sequences instead of MySQL auto_increment columns ? Leaving aside more exotic ways of sequences even pure sequential value as in the case above can be quite helpful – in MySQL 5.0 you may with to use them instead of auto_increment with Innodb tables to avoid short term “table level locks” which innodb sets when Insert is happening in the table with auto_increment values. It is also helpful if you need to decouple ID generation from storing the data – for example IDs are generated on central server and when stored on number of servers or to number of individual tables.
Finally your own sequences allow you to generate multiple sequence values on demand with single statement for one or more sequences, which also can be helpful:
mysql> select seq('one'),seq('two'),seq('one'),seq('one');
| seq('one') | seq('two') | seq('one') | seq('one') |
| 102 | 1002 | 103 | 104 |
1 row in set (0.00 sec)
I should note this sequence generation requires serialization, though it is short term but it may still become the bottleneck for application with high sequence use rate. For such heavy duty apps I would use another approaches – in particular allocating “ranges” of sequences and caching them in applications, using UUID_SHORT() and other methods which do not require global lock for each time new sequence value needs to be retrieved.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.