How to deal with the use of mysql self-increasing primary key

This article introduces the relevant knowledge of "how to deal with mysql self-increasing primary key exhaustion". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Treatment:

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

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

When using the bigint(unsigned) type, a large amount of data is inserted every 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 continue to run for 100 years, the amount of data in a single table is: 10000*3600*24*365*100= 31536000000, which is much less than 2^64 and will not be used up at all.

examples

When a self-added primary key field reaches the upper limit of the defined type, an error of primary key conflict will be reported if a row of records is inserted.

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 data, the AUTO_INCREMENT of this table does not change (it is still 4294967295), which causes the second insert statement to get the same self-increasing id value, and then attempts to execute the insert statement, reporting a primary key conflict error.

The content of "mysql self-increasing primary key used up how to deal with" is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!