Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
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. –  Daniel Lyons Feb 29 '12 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 '12 at 6:39
1  
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. –  Daniel Lyons Feb 29 '12 at 6:56
    
superche I hope you took @Daniel's excellent advice. –  Jack Douglas Mar 16 at 21:34
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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