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

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!

You may also be interested in:
  • How to modify the starting value of mysql auto-increment ID
  • How to set mysql auto-increment id back to 0
  • Solution to the problem of self-increment ID in MySQL table
  • Troubleshooting and solutions for MySQL auto-increment ID oversize problem
  • What you need to know about MySQL auto-increment ID
  • Example of MySQL auto-increment ID exhaustion
  • Summary of some small issues about MySQL auto-increment ID
  • What to do if MySQL self-incrementing ID runs out

<<:  Vue's various implementation methods for modifying parent component props through child components

>>:  How to build a tomcat image based on Dockerfile

Recommend

Screen command and usage in Linux

Screen Introduction Screen is a free software dev...

Pycharm2017 realizes the connection between python3.6 and mysql

This article shares with you how to connect pytho...

Talking about Less and More in Web Design (Picture)

Less is More is a catchphrase for many designers....

How to use mysqldump for full and point-in-time backups

Mysqldump is used for logical backup in MySQL. Al...

How to check whether a port is occupied in LINUX

I have never been able to figure out whether the ...

Complete steps to install mysql5.7 on Mac (with pictures and text)

I recently used a Mac system and was preparing to...

VMware kali virtual machine environment configuration method

1|0 Compile the kernel (1) Run the uname -r comma...

Javascript File and Blob Detailed Explanation

Table of contents File() grammar parameter Exampl...

Summary of the differences between Vue's watch, computed, and methods

Table of contents 1 Introduction 2 Basic usage 2....

Detailed explanation of crontab scheduled execution command under Linux

In LINUX, periodic tasks are usually handled by t...

Detailed explanation of CSS BEM writing standards

BEM is a component-based approach to web developm...

How to modify the default encoding of mysql in Linux

During the development process, if garbled charac...

Understanding and application scenarios of enumeration types in TypeScript

Table of contents 1. What is 2. Use Numeric Enume...