Solution to running out of MySQL's auto-increment ID (primary key)

There are many types of auto-increment IDs used in MySQL, and each auto-increment ID has an initial value set. Generally, the initial value starts from 0 and then increases in a certain step size (usually by 1). Generally, we use int(11) as the auto-increment ID of a data table. In MySQL, as long as the byte length of this number is defined, there will be an upper limit.

What should I do if MySQL's auto-increment ID (primary key) is used up?

If we use int unsigned (int, 4 bytes), we can calculate the maximum currently declared auto-increment ID. Since int unsigned is defined here, the maximum value can reach 2 to the power of 32 - 1 = 4294967295.

Here is a little trick. When creating a table, you can directly declare the initial value of AUTO_INCREMENT as 4294967295.

create table `test` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=4294967295;

SQL Insert Statement

insert into `test` values ​​(null);

When I tried to insert another piece of data, I got the following abnormal result.

[SQL] insert into `test` values ​​(null);
[Err] 1062 - Duplicate entry '4294967295' for key 'PRIMARY'

It means that when the data is inserted again, the auto-increment ID used is still 4294967295, and a primary key conflict error is reported. This means that after the ID value reaches the upper limit, it will no longer change. 4294967295, this number can handle most scenarios. If your service frequently inserts and deletes data, there is still a risk of running out. It is recommended to use bigint unsigned, which is a large number.

bigint unsigned is an integer data (all numbers) ranging from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807), and its storage size is 8 bytes.

However, there is another situation. What happens if the primary key is not explicitly declared when creating a table?

If this is the case, InnoDB will automatically create an invisible row_id of 6 bytes for you, and InnoDB maintains a global dictsys.row_id, so tables without a primary key defined share the row_id. Each time a piece of data is inserted, the global row_id is used as the primary key id, and then the global row_id is increased by 1.

The global row_id is implemented using the bigint unsigned type, but actually only 6 bytes are reserved for row_id. This design will have a problem: if the global row_id keeps increasing until it reaches 2 to the 48th power - 1, and then increases by 1, the lower 48 bits of row_id will all be 0. As a result, when a new row of data is inserted, the row_id obtained will be 0, and there is a possibility of a primary key conflict.

Therefore, in order to avoid this hidden danger, each table needs to have a primary key.

Summarize

After the auto-increment ID of the database table reaches the upper limit, its value will not change when it is applied again. Continuing to insert data will result in a primary key conflict error. Therefore, when designing a data table, try to choose the appropriate field type based on business needs.

The above is the detailed content of the solution to the problem that MySQL's auto-increment ID (primary key) has been used up. For more information about MySQL's auto-increment ID (primary key), please pay attention to other related articles on 123WORDPRESS.COM!