sequence creation

[Sri In]

Hi,
Can anybody please help me how to generate sequence in
my sql 5.1.

[Jeanne Boyarsky]

Does MySql support sequences? I thought it didn't. In which case you will need to create a table to hold the sequence value.

[Vamsi Mohan]

Please find the sequence syntax:

CREATE SEQUENCE <sequence name> START WITH <start no.> MAXVALUE <max no.> INCREMENT BY <value> CACHE value;

Please use triggers, while inserting the records in the database. So automatically trigger will be triggered before inserting the record in the table.

[Jan Cumps]

Vamsi Mohan.Technology wrote:Please find the sequence syntax:

CREATE SEQUENCE <sequence name> START WITH <start no.> MAXVALUE <max no.> INCREMENT BY <value> CACHE value;

Please use triggers, while inserting the records in the database. So automatically trigger will be triggered before inserting the record in the table.

Does MySql 5.1 support that? I could not find it in the reference.

[ujjwal soni]

Hi,

Use the auto_increment feature in mysql. See the create table documentation - http://dev.mysql.com/doc/refman/5.1/en/create-table.html

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.

[Vamsi Mohan]

Yes, Mysql supports sequences. Please use appropriate syntax for creating sequences.

[Paul Sturrock]

Does it? Is this new functionality? The documentation seems to suggest otherwise.

[ujjwal soni]

Hi,

Mysql does not have any specific keyword for sequences....