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?

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.