List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:December 27 2009 10:10pm
Subject:Re: last_insert_id
View as plain text  
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


Thread
last_insert_idVictor Subervi27 Dec
  • Re: last_insert_idMattia Merzi27 Dec
    • Re: last_insert_idVictor Subervi27 Dec
      • Re: last_insert_idMichael Dykman27 Dec
        • Re: last_insert_idVictor Subervi27 Dec
          • Re: last_insert_idGary Smith27 Dec
            • Re: last_insert_idVictor Subervi27 Dec
            • Re: last_insert_idCarsten Pedersen27 Dec
  • Re: last_insert_idSteve Edberg27 Dec
    • Re: last_insert_idGary Smith27 Dec
      • Re: last_insert_idMark Goodge27 Dec