Solution to MySQL auto_increment Limitations

By default, MySQL's auto_increment column has a maximum number of 2,147,483,647. In practice, this number may even be less since the next NULL insert to the table will use the next value of the highest insert number. Consequently, once you have reached the maximum number, the next null insert will cause an error because of the occurrence of a duplicate key.

The solution to MySQL's auto_increment limitations is to create a primary key as an UNSIGNED BIGINT(20) type. As an example for the table "mytest", issue the following MySQL command:

>CREATE TABLE mytest(p_id BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT, name CHAR(10) );

The UNSIGNED BIGINT(20) type has a maximum number of 18,446,744,073,709,551,616. A SIGNED BIGINT(20) type has a maximum number of 9,223,372,036,854,775,807. Since you will be using this as a non-duplicating primary key in your table, most likely you will be using the UNSIGNED BIGINT(20) type since there is no need for storing negative values.