How to solve the problem when mysql self-increasing ID is used up

MySQL has run out of self-increasing IDs, what should I do?

As a programmer, I wonder if you have ever encountered a problem like this during a job interview.

Zhang Gong is a Java programmer. He recently went to an Internet company for an interview, and the interviewer asked him such a question.

Interviewer: "Have you ever used mysql? Do you use an auto-increment primary key or a UUID as the primary key ID in your data table?"

Zhang Gong: "You use an auto-increment primary key"

Interviewer: "Why is it an auto-incrementing primary key?"

Zhang Gong: "Because it uses an auto-incrementing primary key, the data is stored sequentially in the physical structure, and the performance is good"

Interviewer: "Then the auto-incremented primary key has reached the maximum value. What should I do if it is used up?" You can go back and wait for the notification."

Today we will talk about what to do if the auto-incremented primary key is used up?

In mysql, the range of int integer type is as follows. The value range of int is: -2^31——2^31-1, that is, -2147483648—2147483647

As shown in the figure:

For example, an unsigned integer can store a range from 0 to 4294967295, which is approximately 4.3 billion. When the auto-incremented id reaches the maximum value, what exceptions will occur if the insertion continues?

Let’s practice it.

First, create a table tb_user, which only contains an auto-increment id

create table  tb_user(id int unsigned auto_increment primary key) ;

Then insert a piece of data into this table:

insert into tb_user values(null);

Use the show command show create table tb_user; Check the table situation:

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

If you are careful, you will find that AUTO_INCREMENT has become 2, but this is far from the maximum value of 4294967295. To make it become 4294967295, you have to insert a lot of records. In fact, No need to go to such trouble, we can directly declare the initial value of AUTO_INCREMENT when creating the table.

Adjust the table creation statement we just created. First delete the table just now, then add auto_increment = 4294967295

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

when creating the table, and then insert a record into the table as well

insert into tb_user values(null);

Similarly, we use the show command to view the table structure of table tb_user:

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

Through

select * from tb_user

we query that the id is 4294967295, which is already the maximum value. If we continue

When trying to insert a piece of data into the table, a primary key conflict exception is reported as shown below.

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

This can show that when inserting again, the auto-increment ID used is still 4294967295, and a primary key conflict exception will be reported.

4294967295, this number can already cope with 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, this number will be large.

Is there any solution? The answer is yes, and the solution is very simple. Change the Int type to the BigInt type. The range of BigInt is as follows

-2^63-1 to 2 ^63-1

-9223372036854775808 9223372036854775807

##Even if 10,000 pieces of data are inserted into the data table every second and run for 100 years, let’s see how much data there is

10000*24*3600*365*100=31536000000000

This number is still far from the upper limit of BigInt, so you can solve the problem by setting the auto-increment ID to BigInt type. .

If you answer the interviewer like this during the interview.

You: "This is not simple. Just change the type of auto-incrementing primary key to BigInt type."

Interviewer: "How do you modify the column online? Data type?"

You:"alter table tb_user change id id bigint;"

Interviewer: "Do you have practical experience?"

You:" … , for most operations, the original table can be read and written.

Concurrent DML operations are not supported for operations such as modifying data types! In other words, if you directly use statements like alter to modify the table data structure online, this table will not be able to perform update operations (delete, update, insert). Therefore, it is not feasible to modify the table structure on the production line.

Is there a better way? We will discuss this issue later.

I wonder if you have noticed such a situation. Although the primary key auto-increment ID starts from 0, that is to say, the range that can be used now is 0~2147483647, but there are some id values ​​in the actual data. It's not continuous.

If the actual production table has a single table with more than hundreds of millions of data, and you want to write data to the data table at this time, the performance will definitely be affected, and you must quickly consider sub-databases and tables.

If the database is divided into tables, the auto-incremented ID of each table cannot be used to globally uniquely identify the data. To support the environment of sharding databases and sharding tables, we need to provide a strategy for generating globally unique ID numbers.

So in practice, there is no way to wait until the auto-incremented primary key is used up.

For a more friendly answer, you may wish to refer to this

Interviewer: "Then the auto-incremented primary key has reached the maximum value. What should I do if it is used up?"

You: I have never encountered this problem before, because we use the int type for auto-increasing primary keys, which generally cannot reach the maximum value, so we need to consider splitting tables and databases.

If the interviewer keeps chasing you and continues to ask you about the key points of sub-database and sub-table, you can answer it in a targeted way, indicating that you have full development experience in this area and I believe you can be useful. Bonus points for this interview.

The above is the detailed content of How to solve the problem when mysql self-increasing ID is used up. For more information, please follow other related articles on the PHP Chinese website!