In fact, this question can be answered in two cases: with a primary key and without a primary key.

International practice, first show the brain map:

02 has a primary key
If your table has a primary key, and set the primary key to auto-increment.

In MySQL, the primary key is generally set to int type. In MySQL, the int type occupies 4 bytes. As a signed bit, the range is [-2 31,2 31-1], which is [-2147483648,2147483647]; if the unsigned bit is unsigned, the maximum value is 2^32-1 , Which is 4294967295.

Create a table with a signed bit as follows:

CREATE TABLE IF NOT EXISTS `t`(
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `url` VARCHAR(64) NOT NULL,
   PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;复制代码123456

Insert a value with the maximum id of 2147483647, as shown in the following figure:

If you continue with the following insert statement at this time:

INSERT INTO t (url) VALUES ('wwww.javafish.top/article/erwt/spring')复制代码12

The result will cause a primary key conflict:

2.1 Solution
Although int 4 bytes, the maximum amount of data can store 2.1 billion. You might think that such a large capacity should not be used up. However, in the Internet era, a large amount of data is generated every day, which is very likely to be achieved.

Therefore, our solution is: change the primary key type to bigint, which is 8 bytes. The maximum amount of data that can be stored in this way is 2^64-1, and I can't count how many. It should be enough for your lifetime anyway.

PS: The 2.1 billion data volume of a single table is obviously unrealistic. Generally speaking, the data volume of 5 million should be divided into tables.

03 No primary key
Another situation is that the primary key is not set when the table is built. In this case, InnoDB will automatically help you create an invisible row_id with a length of 6 bytes. The default is unsigned, so the maximum length is 2^48-1.

In fact, InnoDB maintains a global dictsys.row_id, so tables without a defined primary key share the row_id, not exclusive to a single table. Every time a piece of data is inserted, the global row_id is regarded as the primary key id, and then the global row_id is incremented by 1.

What happens if the database auto-increment ID runs out in this case?

1. Create a table t with no primary key display settings:

CREATE TABLE IF NOT EXISTS `t`(
   `age` int(4) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;复制代码1234

2. Obtain the process ID of mysql through the ps -ef|grep mysql command, then execute the command, and modify the row_id to 1 through gdb. PS: If there is no gdb, install it on Baidu

sudo gdb -p 16111 -ex 'p dict_sys->row_id=1' -batch复制代码12

The following picture is correct:

3. Insert three pieces of data:

insert into t(age) values(1);insert into t(age) values(2);insert into t(age) values(3);复制代码1234

Database data at this time:

4. gdb modifies row_id to the maximum value: 281474976710656

sudo gdb -p 16111 -ex 'p dict_sys->row_id=281474976710656' -batch复制代码12

5. Insert three more data:

insert into t(age) values(4);insert into t(age) values(5);insert into t(age) values(6);复制代码1234

Database data at this time:

analyze:

At the beginning, row_id is set to 1, and the row_id for inserting three pieces of data 1, 2, and 3 should also be 1, 2, 3; this is no problem.

Then set row_id to the maximum value, and then insert three pieces of data. The result of the database at this time is: 4, 5, 6, 3; you will find that 1, 2 is overwritten.

The row_id of the inserted values 4, 5, and 6 after the row_id reaches the maximum value are 0, 1, and 2, respectively; since the value of row_id is 1, 2 already exists, the latter value of 5, 6 will overwrite the row_id of 1, The value of 2.

Conclusion: After row_id reaches the maximum value, the calculation will restart from 0; the data inserted before will be overwritten by the data inserted later, and no error will be reported.

04 Summary After the
database auto-increment primary key is used up, there are two situations:

If there is a primary key, report a primary key conflict. If there is
no primary key, InnDB will automatically generate a global row_id. When it reaches the maximum value, it will start counting from 0. When the row_id is the same, the new data will overwrite the old data. Therefore, we still try to set the primary key for the table.