Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "mysql self-growing id has run out of how to solve it". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mysql self-growth id is used up, what should I do?

As a programmer, I don't know if you have encountered any similar problems during the job interview.

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

Interviewer: "have you used mysql? do you use the self-increasing primary key or UUID for the primary key id of your datasheet?"

Zhang Gong: "self-increasing primary key is used."

Interviewer: "Why do you add the primary key?"

Zhang Gong: "because the self-increasing primary key is used, the data is sequentially stored in the physical structure, and the performance is good."

Interviewer: "then the self-increasing primary key has reached the maximum value, what if it is used up?"

Zhang Gong: "after you use it, you will run out of it, and then apply."

Interviewer: "you can go back and wait for the notice."

Today, let's talk about what to do when this self-increasing primary key runs out.

The range of mysql,int integer is as follows:-2 ^ 31-- 2 ^ 31-1, that is,-2147483648Mui 2147483647

As shown in the figure:

Take the unsigned integer as an example, the storage range is 04294967295, about 4.3 billion. When the self-incrementing id reaches the maximum, what exception will occur if you continue to insert?

Let's put it into practice.

First, create a table tb_user that contains only one self-incrementing id

Create table tb_user (id int unsigned auto_increment primary key)

Then insert a piece of data into the table:

Insert into tb_user values (null)

Use the show command show create table tb_user; to view the table:

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. If you want to make it 4294967295, you have to insert a lot of records. In fact, it is not so troublesome. We can directly declare the initial value of AUTO_INCREMENT when creating the table.

To adjust our table creation statement, delete the table first, and then add auto_increment = 4294967295 when creating the table.

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

Then insert a record into the table as well

Insert into tb_user values (null)

Similarly, we look at the table structure of the table tb_user through the show command:

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

Pass through

Select * from tb_user

We found that the id is 4294967295, which is already the maximum value. At this time, if again

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

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

This shows that when you insert again, the self-incrementing ID used is still 4294967295, and an exception of primary key conflict will be reported.

4294967295, this number can handle most scenarios, if your service will insert and delete data frequently, there is still a risk of running out of data.

It is recommended to use bigint unsigned, and this number is large.

The answer is yes, and the solution is also very simple. Change Int type to BigInt type. The scope of BigInt is as follows.

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

-9223372036854775808 9223372036854775807

Even if you insert 10000 pieces of data into the data table every second, run it for 100 years to see how much data there is.

10000243600365100315360000000

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

If that's how you answer the interviewer during the interview.

You: "this is not easy, change the type of self-increasing primary key to BigInt type can be solved!"

Interviewer: "how do you change the data type of the column online?"

You: alter table tb_user change id id bigint;

Interviewer: "do you have any practical experience?"

You: ". No actual operation."

It should be noted that this method is only supported in myl5.6+. Mysql supports online modification of database tables. In the process of modifying tables, the original table can be read or written for most of the operations.

For modifying data types, concurrent DML operations are not supported! In other words, if you directly use statements like alter to modify the data structure of the table online, it will prevent the table from updating (delete, update, insert). Therefore, it is not feasible to implement the scheme of modifying the table structure on the production line.

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

I do not know if you have noticed such a situation, although the primary key self-increment ID starts from 0, that is to say, the range that can now be used is 0such 2147483647, but some id values in the actual data are not continuous.

If there are more than 100 million data in a single table in the actual production table, if you want to write data to the data table again, the performance must be affected, so you have to consider sub-database and sub-table as soon as possible.

Once the database is divided into tables, we cannot rely on the self-increasing id of each table to globally and uniquely identify the data. At this point, we need to provide a globally unique id number generation strategy to support the environment of sub-libraries and sub-tables.

Therefore, in practice, it is impossible to wait for the self-increment primary key to run out.

For a more friendly answer, please refer to this.

Interviewer: "then the self-increasing primary key has reached the maximum value, what if it is used up?"

You: this problem has not been encountered, because since the primary key we use int type, generally can not reach the maximum value, it is necessary to consider sub-table sub-library.

If the interviewer pursues you and continues to ask you about the main points of the sub-database and sub-table, you can also answer pertinently, indicating that you have full development experience in this area. I believe you can add points to this interview.

This is the end of the content of "mysql self-growth id runs out of id". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.