As you probably now if you reading this blog, MySQL does not support SQL sequences. Logically, it is very easy to implement something equivalent to a sequence using transaction with InnoDB with a simple 2 columns table, name and value. The only problem is that a sequence update is usually composed of two SQL statements, a SELECT to read the value and an UPDATE to increment it. Fairly you would say, use a transaction to wrap the 2 statements together but then, you are probably aware that you cannot manage transactions in a function with MySQL. A clean sequence function seemed out of reach up to recently, when Brian Morin showed me a trick that solves the issues. Here how to create a get_next_val function to a sequence.
delimiter // drop function if exists true_function // create function true_function(p_param int) returns int deterministic sql security invoker return true // drop function if exists get_next_value// create function get_next_value(p_name varchar(30)) returns int deterministic sql security invoker begin declare current_val integer; update mysql.sequences set value = value + 1 where name = p_name and true_function((@current_val := mysql.sequences.value) is not null); return @current_val; end// delimiter ;
The interesting stuff here is that the assignment of current_val is done inside the same SQL statement and uses the actual value, before the update. The true_function is a trick to make the optimizer not to bother with the variable assignment. The sequence table is very simple:
mysql> show create table mysql.sequences\G *************************** 1. row *************************** Table: sequences Create Table: CREATE TABLE `sequences` ( `name` varchar(30) NOT NULL, `value` int(10) unsigned DEFAULT '0', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
now, you get a sequence much like with other database and the transaction is implicit since everything is done inside a single statement.
Using the function is easy:
mysql> select mysql.get_next_value('user_id'); +---------------------------------+ | mysql.get_next_value('user_id') | +---------------------------------+ | 18 | +---------------------------------+ 1 row in set (0.01 sec)
For this recipe, if you are using explicit transactions, I recommend setting the isolation level not under “Repeatable read” since it could caused the same sequence value to be return twice. For the about same reason, isolation level under “Repeatable read” cannot be used for replication.