Or insert a record, get the lastinsertid, and delete the record. Keeps
the file small
and guarantees a unique number every time.
Cal Evans wrote:
> Make a table in your database called ID
> Create table ID (ID bigint auto_increment not null,
> lockedBy varchar(10) null};
> Then write code in your favorite language that:
> Checks to see if the record is locked by another user. if so, loop.
> Updates the record with a unique session identifier (IP ADDRESS?) in the
> LockedBy field.
> Checks to see that the lock on the record is theirs. If not, loop.
> Grab the current value in the ID field and store it for use.
> Update the record by incrementing the ID by 1 and erasing the lock on the
> This should allow you to have a "Serial Assigned Key" or SAK global to your
> database. If you need it to span databases then I suggest you put this table
> in it's own database.
> -----Original Message-----
> From: Jean-Philippe Côté [mailto:cotej@stripped]
> Sent: Monday, April 09, 2001 11:40 PM
> To: mysql@stripped
> Subject: Global AUTO_INCREMENT ???
> Hi everybody,
> I'm new to MySQL and I'm trying to figure out a way of
> automatically generating a sequence of unique id numbers
> in many tables. I need to get new id's at any time
> and possibly from different connections. As I understand,
> AUTO_INCREMENT does this, but the sequence it generates
> is not global. That is, if table T1 and T2 have AUTO_INCREMENT
> id columns, then a row in T1 could have the same id as
> a row in T2. I think I could circumvent this by setting
> the AUTO_INCREMENT sequence so that it starts at say 1000000
> in T1, 2000000 in T2, 3000000 in T3 and so on. The chances
> that rows in different tables have the same id would be
> rather slim. But I don't really like this solution. Does anybody
> knows a cleaner and nicer way of doing this ?
> Thanks in advance.