List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 28 1999 5:19pm
Subject:Re: AUTO_INCREMENT not guaranteed unique after DELETE
View as plain text  
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
Thread
AUTO_INCREMENT not guaranteed unique after DELETEchris28 Dec
  • Re: AUTO_INCREMENT not guaranteed unique after DELETEJames Lyon28 Dec
  • Re: AUTO_INCREMENT not guaranteed unique after DELETEChris Shenton28 Dec
  • Re: AUTO_INCREMENT not guaranteed unique after DELETEJames Lyon28 Dec
  • Re: AUTO_INCREMENT not guaranteed unique after DELETEPaul DuBois28 Dec
    • Re: AUTO_INCREMENT not guaranteed unique after DELETEMatthias Urlichs28 Dec
      • Re: AUTO_INCREMENT not guaranteed unique after DELETEPaul DuBois28 Dec
  • Re: AUTO_INCREMENT not guaranteed unique after DELETEChris Shenton28 Dec
    • Re: AUTO_INCREMENT not guaranteed unique after DELETEPeter J. Schoenster29 Dec
      • Re: AUTO_INCREMENT not guaranteed unique after DELETEPaul DuBois29 Dec
      • Re: AUTO_INCREMENT not guaranteed unique after DELETEhypnos29 Dec
        • Re: AUTO_INCREMENT not guaranteed unique after DELETEKen Scott29 Dec