Re: How to generate sequence in MySql

[Nishant Deshpande]

create a real sequence as follows:
"""
create table sequence (
name varchar(64) primary key,
last_val int unsigned not null
) engine=myisam;

INSERT INTO sequence (name, last_val) VALUES ('seq', 0);
"""

now when you want to get the next number in the sequence do:
"""
UPDATE sequence SET last_val=@val:=last_val+1 WHERE name='table_changes';
"""

the key here is that the sequence table is myisam, and so does not respect transactions. if you use a table that respects transactions, you will block when there are multiple transactions trying to get a sequence, whereas what you want is to just get a sequence.