How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

Question

For example, I have a table for user registration:

CREATE TABLE IF NOT EXISTS test_user ( 
 user_id int(10) unsigned NOT NULL AUTO_INCREMENT, 
 user_name varchar(50) NOT NULL, 
 PRIMARY KEY (user_id), 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want the user_id field ID numbers to be continuous, without gaps.

Failed transactions will occupy some ID numbers, is there any way around this?

• Daniel Lyons: It is likely that int(10) is larger than you think it is: your highest user ID is 4,294,967,295. It seems unlikely to me that you're going to wind up with more than 1/2 the world population in your database, so I would relax and ignore the gaps.
• superche: @DanielLyons Actually we try to use this id as part of a message in distributed system communication. When local app found there is a id gap, it should ask it from master app to ensure all messages are delivered successfully.
• Daniel Lyons: I strongly recommend you redesign your system to eliminate this additional, hidden responsibility from your user IDs and find a less clever, less brittle approach to synchronizing your systems. If you don't, I predict this will be only the first of many difficulties you'll face.
• Jack Douglas: superche I hope you took @Daniel's excellent advice.

Answer 1

An auto increment column will always provide the highest available id for a new user in your case.

However re-using missing numbers from failed transactions is not possible in a single step, since the primary key will probably have been used in other tables which will need to be updated.

There are no performance issues associated with having missing numbers, and there are a common occurrence. This can only be an issue if you have more failed transactions than those which complete, however there I would recommend you set a larger size for your primary key.

• Damian Yerrick: The performance issue of having missing numbers is that some environments are much slower at processing BIGINT than INT, especially if 99.9% of INSERT statements "fail" due to ON DUPLICATE KEY UPDATE.