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

This article mainly explains "what to do when the self-increment primary key in mysql is used up". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what to do when the self-increment primary key in mysql runs out"!

In general, we do not allow a single table to store so much data, so this problem generally does not occur.

How to handle it:

1. You can use the bigint (unsigned) type as the self-increasing primary key.

If you really store that much data in a table, or cause confusion in the self-incrementing primary key during data migration.

2. When using bigint (unsigned) type, a large amount of data is inserted per second, and the amount of data in a single table can still be stored for a long time.

If we insert 1W of data per second and run continuously for 100 years, the amount of data per table is: 100003600243651000000000, which is far less than 2 ^ 64, it will not be used up at all, if the operation is correct.

Example

If a row of records is inserted after the primary key field reaches the upper limit of the defined type, an error of primary key conflict will be reported.

CREATE TABLE t (id INT UNSIGNED auto_increment PRIMARY KEY) auto_increment = 4294967295 / insert INTO t VALUES (NULL); INSERT INTO t VALUES (NULL)

After the first insert statement successfully inserts the data, the AUTO_INCREMENT of the table does not change (still 4294967295), which causes the second insert statement to get the same self-increment id value, and then attempts to execute the insert statement, reporting a primary key conflict error.

At this point, I believe you have a deeper understanding of "what to do when the self-increment primary key in mysql is used up". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.