As time passes and the database has a lot of INSERTs, the auto increment primary key IDs of the tables can get quite big, especially if the INSERTs are automated by some automated process. I know that a MySQL INT field (for example) has this range: signed from -2147483648 to 2147483647, or unsigned from 0 to 4294967295. It is difficult to reach those limit values ​​(but not impossible). Is there a command or process that normalizes the IDs to smallest possible values? If the value of an auto increment ID of a table arrived at 967295, but then for some reason the last 967200 INSERTs are deleted, how do I normalize the table so that the next auto increment is 96, initially respecting all secondary keys and maintaining the intact structure?

CodePudding user response：

Is there a command or process that normalizes the IDs to smallest possible values? If the value of an auto increment ID of a table arrived at 967295, but then for some reason the last 967200 INSERTs are deleted, it is possible to normalize the table so that the next auto increment is 96, initially respecting all secondary keys and maintaining the intact structure?

There is no command because primary keys aren't supposed to change.

You could write a program to periodically scan the table for large gaps and then change the primary keys to use the next available one. And you'd also have to change everywhere it is used as a foreign key.

This will not work with an autoincremented primary key. Autoincrement is a simple counter, it will not jump gaps for you. You'd have to normalize every entry in the table every time, then reset the autoincrement to max(id). Rewriting most of the rows in the table will be slow and will cause locks. Alternatively, you'd write an insert trigger to find the next available key; slow and complicated.

This assumes there is nothing outside the database referring to the primary key. For example, http://example.com/thing/123 where 123 is an ID in the things table.

There are two better solutions.

1. Use a UUID primary key.
2. Use a bigint primary key.

UUID primary keys solve and create problems. They're awkward in MySQL. And they require 16 bytes, 12 more than an int.

Bigints work just like ints. They only take 4 more bytes. They're the native integer type on any modern hardware. They're a simple upgrade that only has to be done once: alter the type of the primary key and its foreign keys. You might run out of 2 billion or even 4 billion primary keys. You're not going to run out of 9,223,372,036,854,775,808.