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

Today, I will talk to you about how to solve the problem when MySQL's self-increasing ID is used up. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

First, create the simplest table that contains only a self-incrementing id and insert a piece of data.

Create table T0 (id int unsigned auto_increment primary key); insert into T0 values (null)

Check the table through the show command show create table t0;

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

You can find that AUTO_INCREMENT has automatically become 2, which is still a long way from running out. We can calculate the maximum self-increasing ID of the maximum currently declared. Since intunsigned is defined here, the maximum can reach the 32 power of 2-1 = 4294967295.

Here is a trick to declare the initial value of AUTO_INCREMENT directly when you create the table

Create table T1 (id int unsigned auto_increment primary key) auto_increment = 4294967295 * insert into T1 values (null)

Again, use the show command to view the table structure of T1

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

As you can see, the AUTO_INCREMENT has become 4294967295. When you try to insert another piece of data, you get the following exception

17:28:03 insert into T1 values (null) Error Code: 1062. Duplicate entry '4294967295' for key 'PRIMARY' 0.00054 sec

Note that when re-inserted, the self-incrementing ID is still 4294967295, reporting the error of primary key conflict.

4294967295, this number is enough to cope with 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, which is a large number.

However, there is another situation where what happens if the declaration primary key is not displayed in the creation table?

If this is the case, InnoDB will automatically create an invisible 6-byte row_id for you, and InnoDB maintains a global dictsys.row_id, so tables with no primary key 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 added by 1.

The global row_id uses the bigint unsigned type in the code implementation, but in fact only leaves 6 bytes for the row_id, there will be a problem in this design: if the global row_id keeps rising until the 48 power of 2-1, then + 1 the lower 48 bits of the row_id are 0, as a result, when inserting a new row of data, the row_id is 0, and there is the possibility of primary key conflict.

After reading the above, do you have any further understanding of how to solve the problem when MySQL's self-added ID is used up? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.