Page 1 of 1
MySQL: auto-incrementing integers
Posted: Tue Jun 29, 2010 10:09 am
by greyhoundcode
I've searched around but can't find a clear answer to what I imagine is a fairly straightforward question: if I have a field (`id`) which is set to auto-increment and it hits the upper limit of the integer range (I know it's very high, but even so!) what happens next?
Will MySQL start again from 0/1?
Re: MySQL: auto-incrementing integers
Posted: Tue Jun 29, 2010 12:31 pm
by AbraCadaver
There is no clear answer because: "The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type."
But in actuality, it will attempt to keep using the max value 2147483647 and you will get a duplicate entry for key error. If you anticipate over 2 billion or so records then you can use a BIGINT and get up to 18446744073709551615 (approx. 18 quintillion).
Re: MySQL: auto-incrementing integers
Posted: Wed Jun 30, 2010 11:41 pm
by greyhoundcode
That pretty much answers the question; a shame that it cannot be set up to simply 'rotate' back to 1 or to use the lowest available integer, such as if rows [with ids] 2 and 4 were deleted then it would automatically fill those rows in.
Anyway, I guess I can achieve something by editing the relevant auto increment field in the information_schema table at the appropriate time.