For example, I have a table for user registration:

   user_id int(10) unsigned NOT NULL AUTO_INCREMENT,  
   user_name varchar(50) NOT NULL,  
   PRIMARY KEY (user_id),  

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?

  • 3
    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. Feb 29, 2012 at 6:21
  • @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.
    – superche
    Feb 29, 2012 at 6:39
  • 3
    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. Feb 29, 2012 at 6:56
  • superche I hope you took @Daniel's excellent advice. Mar 16, 2014 at 21:34

1 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.

  • 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. Aug 20, 2015 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.