List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:May 20 1999 6:21am
Subject:Re: autonumbering
View as plain text  
On Thu, May 20, 1999 at 01:59:18AM -0400, Jim Faucette wrote:
> sean hynes wrote:
> > Does mysql have an autonumbering system where if you delete row 6 it will
> > start on 7 as the next record?
> > And how do you start an autonumber at 9900? thanks
> 
> No. This and other variations of auto_increment are on the TODO list.
> Currently people needing this create a table with a num field, then:
> LOCK table with num field
> UPDATE table with num to num + 1
> SELECT num from that table
> UNLOCK table
> use value in the insert into your primary table

A better way to do it is this, from the manual:

`LAST_INSERT_ID([expr])'

     If `expr' is given as an argument to `LAST_INSERT_ID()' in an
     `UPDATE' clause, then the value of the argument is returned as a
     `LAST_INSERT_ID()' value.  This can be used to simulate sequences:

     First create the table:

          mysql> create table sequence (id int not null);
          mysql> insert into sequence values (0);

     Then the table can be used to generate sequence numbers like this:

          mysql> update sequence set id=LAST_INSERT_ID(id+1);

     You can generate sequences without calling `LAST_INSERT_ID()', but
     the utility of using the function this way is that the ID value is
     maintained in the server as the last automatically-generated
     value.  You can retrieve the new ID as you would read any normal
     `AUTO_INCREMENT' value in *MySQL*.  For example,
     `LAST_INSERT_ID()' (without an argument) will return the new ID.
     The C API function `mysql_insert_id()' can also be used to get the
     value.

To start at 9900, of course, you would insert 9900 instead of 0 into the
table.

Tim
Thread
autonumberingsean hynes20 May
  • Re: autonumberingJim Faucette20 May
    • Re: autonumberingThimble Smith20 May