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?
MySQL: auto-incrementing integers
Moderator: General Moderators
- greyhoundcode
- Forum Regular
- Posts: 613
- Joined: Mon Feb 11, 2008 4:22 am
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: MySQL: auto-incrementing integers
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).
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.
- greyhoundcode
- Forum Regular
- Posts: 613
- Joined: Mon Feb 11, 2008 4:22 am
Re: MySQL: auto-incrementing integers
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.
Anyway, I guess I can achieve something by editing the relevant auto increment field in the information_schema table at the appropriate time.