MySQL: auto-incrementing integers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
greyhoundcode
Forum Regular
Posts: 613
Joined: Mon Feb 11, 2008 4:22 am

MySQL: auto-incrementing integers

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL: auto-incrementing integers

Post 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).
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
greyhoundcode
Forum Regular
Posts: 613
Joined: Mon Feb 11, 2008 4:22 am

Re: MySQL: auto-incrementing integers

Post 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.
Post Reply