List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 29 1999 2:09am
Subject:Re: AUTO_INCREMENT not guaranteed unique after DELETE
View as plain text  
At 7:07 PM -0700 1999-12-28, Peter J. Schoenster wrote:
>On 28 Dec 99, at 15:18, Chris Shenton wrote:
>
>>  Paul DuBois <paul@stripped> writes:
>>
>>  > 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.)
>>
>>  Can you give me some pointers to more info on this type and how to
>>  use/invoke it? Thanks for all the help, gang.
>
>
>Ummm ... I've been reading this and I sit here comfortable that
>I understand what is going on.  But just to be sure, I hope
>someone would show the errors of my way if I am wrong.
>
>Here is what I understand has been said:
>
>The AUTO_INCREMENT (AI) "function" returns 1 value higher than
>the last value in the AI field.

Prior to MySQL 3.23, the next AUTO_INCREMENT value is the maximum value
currently in the table, plus 1.

As of MySQL 3.23, the next AUTO_INCREMENT value is the
previously-generated value, plus 1.  In this case, values are not
reused, even if you delete the row which currently has the maximum
value.  Note: This behavior requires that the table have the MyISAM storage
type, not the older ISAM storage type.  You can convert a table like
this:  ALTER TABLE tbl_name TYPE = MYISAM

>
>If my last row_id has a value of 10 and I insert another row
>then the next row_id will have a value of 11.
>
>But ... but .. if I delete row 10 and then add another row, then
>the AI will return a value of 10 (since I deleted 10 there is no
>more 10 and 9 is the highest value).

True prior to MySQL 3.23.

>
>This does not seem to me to allow for the insertion of 2 values
>in my row_id column with the same value.

Always true, because an AUTO_INCREMENT column must be a PRIMARY KEY
or UNIQUE index.  Duplicates are not allowed in any case.

>
>But ... but .. if yesterday I saw that "Mike's Garage" had a
>row_id of 234 that does not mean that tomorrow Mike will have
>the same row_id (I might delete rows with a row_id less than 234
>and then it seems all numbers are recalculated).


That's incorrect.  AUTO_INCREMENT values are not recalculated unless
you take steps to recalculated them yourself.  (For example, if you
drop and then re-add the column, the sequence numbers will be recalculated.
This is one way to reclaim unused numbers that result from deleting
rows.  However, you do NOT want to do this if the sequence numbers
are used for joining rows in your table to another table, because you'll
destroy the correspondence.)


-- 
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