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
