Gary Smith wrote:
Steve Edberg wrote:
(2) autoincrement values are not reused after deletion, so if you
deleted the record with ID=1000 inserted in (1), the next
autoincrement would still be 1001, even if the existing records are
IDs 1,2,3. This is usually the desired behavior, but again, may not be
what *you* need.
Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch
which changes this behaviour, or is my mind dribbling out of my ears?
As far as I'm aware there's no mode to change the default behaviour, but
you can always reset the autoincrement value:
ALTER TABLE tbl AUTO_INCREMENT = n;
Do that, and the next inserted record will have id = n, provided that n
is greater than the current maximum value. If, on the other hand, n is
lower than or equal to the current maximum value, the next id will be
the next value higher than the current maximum. So
ALTER TABLE tbl AUTO_INCREMENT = 1;
on a non-empty table is functionally equivalent to
ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l
(which isn't valid SQL, so don't try it!)
If you want to reuse autoincrement values above the current maximum,
therefore, you can achieve that in practice by resetting the
autoincrement value prior to any insertion.
What you can't do, though, is get autoincrement to insert values into
the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you
issue
ALTER TABLE tbl AUTO_INCREMENT = 1;
or
ALTER TABLE tbl AUTO_INCREMENT = 6;
then the next inserted id will still be 10, not 6.
Mark
--
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
