List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 13 2005 6:10pm
Subject:RE: create sequence
View as plain text  
"Scott Purcell" <spurcell@stripped> wrote on 01/13/2005 01:05:01 PM:

> I understand the auto-increment capability, but how would one share 
> it amount three different tables?
> 
> Would one have to do a rig, and create an extra table with increment
> in it, then get that value to update table 1, then repeat process 
> again to get new increment and put it into table 2, etc.?
> 
> I am used to asking for the sequence.nextId, then using it in any 
> table I need.
> 
> Am I missing something here, or does someone know how to handle this
> in multiple tables?
> 
> Scott
> 
> 

Nope, you aren't missing it. Auto_increment is table-specific. 

If you want to auto-generate a value so that no two-or-more tables share 
the same value, you will need to code that facility using table locks or 
transaction boundaries and a separate table. There is no global 
"sequence"-type object in MySQL.

How you create and increment that number on your common table is up to you 
(add new records, read/increment/update, ...)
But you _will_ need to either lock/unlock that sequencing table or wrap 
your get-next-number process inside a transaction so that you serialize 
access to the data on your sequencing table.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Thursday, January 13, 2005 11:38 AM
> To: Scott Purcell
> Cc: mysql@stripped
> Subject: Re: create sequence
> 
> 
> You want AUTO_INCREMENT, 
> http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html.
> 
> PB
> 
> -----
> 
> Scott Purcell wrote:
> 
> >Hello,
> > 
> >I would like to create a sequence object in mysql that I could use 
> in multiple tables through a application I am developing.
> > 
> >Could someone please send my the syntax of how to create a simple 
> sequence that starts at 1 increments by 1. 
> >
> >thanks,
> >
> > 
> >
> > 
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
create sequenceScott Purcell13 Jan
  • Re: create sequencePeter Brawley13 Jan
  • Re: create sequenceJochem van Dieten13 Jan
RE: create sequenceJay Blanchard13 Jan
RE: create sequenceScott Purcell13 Jan
  • RE: create sequenceSGreen13 Jan
  • RE: create sequenceFrank Bax13 Jan