At 9:38 AM -0800 1999-12-11, J C Lawrence wrote:
>On Sat, 11 Dec 1999 11:29:50 -0600
>Paul DuBois <paul@stripped> wrote:
>
>> At 9:16 AM -0800 1999-12-11, J C Lawrence wrote:
>>> Given an auto_increment column, can I trust it to do the right
>>> thing and roll back to 0 when the max value of the typ is
>>> exceeded? What happens if 0 is occupied? Does it then find the
>>> next available free number?
>
>> No. You should use a type that is large enough to hold the
>> largest value you expect to have. If the sequence reaches the
>> largest value, the next attempt to generate a sequence value
>> results in an error.
>
>This can be expensive.
>
>I have a long lived DB which potentially has a high rate of insert
>and deletes with the total number of records probably numbering in
>the middle millions. To validly use an auto-increment as a
>sustainable key, I'd have to regularly regen the auto_increment
>collumn for all umpty million collumns. Ouch.
I suspect that's not as expensive as the performance hit you'd take
were the database engine to be doing what you seem to suggest:
scan the table for the first hole in the sequence so that it can
reuse an unused record. You'd have to do that for *every* insert,
and it potentially would need to scan several million records.
Regenerating the sequence under the circumstances you describe need
be done only once every several quadrillion inserts.
>
>>> Consider the case of an auto_increment unsigned bigint. What
>>> happens if the last value assigned was 0xFFFFFFFFFFFFFFFF and you
>>> add a new row? What value does it get? What if there is a row
>>> with value 0, and value N (say 5) is unassigned?
>
>> You cannot have a row with value 0 in an auto-increment column.
>> Assigning 0 is like assigning NULL - MySQL generates a sequence
>> number and assigns that instead.
>
>Umm, my mistake. Make that a 1.
If the highest number assigned is the final value in the sequence
allowed by the underlying datatype, the next insert of NULL or 0
into the column results in an error.
--
Paul DuBois, paul@stripped
