What is the biggest ID number that autoincrement can produce in mysql

Solution 1:

If you are worried about it growing out of bounds too quickly, I would set the PK as an UNSIGNED BIGINT. That gives you a max value of 18446744073709551615, which should be sufficient.

Solution 2:

                    | Min. (inclusive)           | Max. (inclusive)
-----------------------------------------------------------------------------
INT Signed (+|-)    |             -2,147,483,648 |              2,147,483,647
-----------------------------------------------------------------------------
INT Unsigned (+)    |                          0 |              4,294,967,295
-----------------------------------------------------------------------------
BIGINT Signed (+|-) | -9,223,372,036,854,775,807 |  9,223,372,036,854,775,806
-----------------------------------------------------------------------------
BIGINT Unsigned (+) |                          0 | 18,446,744,073,709,551,615

MySQL reference.

If you have MySQL table with column ID (INT unsigned) with auto_increment, and the table has 4,294,967,295 records, then you try to insert 1 more record, the ID of the new record will be automatically changed and set to the max which is "4,294,967,295", so you get a MySQL error message Duplicate entry '4294967295' for key 'PRIMARY', you will have duplicated IDs if the column is set as Primary Key.

2 Possible Solutions:

  1. Easy Approach: Extend the limits, by setting the ID to BIGINT unsigned, just like what Dan Armstrong said. Although this doesn't mean it's unbreakable! and performance might be affected when the table gets really large.
  2. Harder Approach: Use partitioning, which is a little more complicated approach, but gives better performance, and truly no database limit (Your only limit is the size of your physical harddisk.). Twitter (and similar huge websites) use this approach for their millions of tweets (records) per day!