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

CentOS 6.5 installation mysql5.7 tutorial

1. New Features MySQL 5.7 is an exciting mileston...

MySQL multi-table join introductory tutorial

Connections can be used to query, update, and est...

Detailed explanation of MySQL EXPLAIN output columns

1. Introduction The EXPLAIN statement provides in...

Let's talk briefly about the changes in setup in vue3.0 sfc

Table of contents Preface Standard sfc writing me...

How to avoid duplication of data when inserting in MySql batch

Table of contents Preface 1. insert ignore into 2...

How to write high-quality JavaScript code

Table of contents 1. Easy to read code 1. Unified...

JavaScript to achieve fancy carousel effect

This article shares two methods of implementing t...

Bug of Chinese input garbled characters in flex program Firefox

Chinese characters cannot be input in lower versio...

Introduction to the process of creating TCP connection in Linux system

Table of contents Steps to create TCP in Linux Se...

Example steps for using AntV X6 with Vue.js

Table of contents 0x0 Introduction 0x1 Installati...

How to view the storage location of MySQL data files

We may have a question: After we install MySQL lo...

Usage of Node.js http module

Table of contents Preface HTTP HTTP Server File S...

How to view and set the mysql time zone

1. Check the database time zone show variables li...