Re: AUTO_INCREMENT not guaranteed unique after DELETE

[Paul DuBois]

At 4:22 PM +0000 1999-12-28, chris@stripped wrote:
>>Description:
>
>When creating a table with a AUTO_INCREMENT field, it appears to
>always set the field's value to one more than the highest *currently*
>in the table.  This causes it to *reuse* a value if I delete the row
>with the highest generated field value, and this reuse means that I am
>not guaranteed unique values over time -- e.g., for customer IDs and
>the like.
>
>I thought that AUTO_INCREMENT would "remember" the last value used
>over all time, and never reuse a value.  The manual is silent on the
>exact behavior here.  Any suggestions on how to do this, or is this a
>bug/misfeature?

This is the behavior of AUTO_INCREMENT columns prior to MySQL 3.23.
As of 3.23, if you use the MyISAM table storage type, the sequence
will be monotonic without values being reused.  (The sequence will,
however, still restart at zero if you empty the table completely.)

-- 
Paul DuBois, paul@stripped