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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

im confused here , i create a table and have 2 columns id and name.

Now id is primary key , not null and auto increment.

Suppose i fire: insert into table (name) values('test');

this will give me:

1  | test

Now if i fire : alter table table_name auto_increment = 5;

this will populate id with 5 on next insert.

now if i delete value of id =5 and again re-insert : insert into table (name) values ('test2');

it gives me :

 6  | test2

why it doesn't give me 5 again?

Also is it not possible that we can't reset the existing auto_increment values to desired one in MySql?

share|improve this question
up vote 1 down vote accepted

I think you will find the answer in MySQL documentation:

Note in particular how the 'innodb_autoinc_lock_mode' option/variable affects this. In MySQL 5.1+ this variable is "1" as default - in 5.0 and before it was "0". The reason to set it to "1" is that it improves performance of replication in some scenarios. If you don't use replication it makes no difference and setting it to "0" should avoid the 'gaps' in the autoincrement counter in most cases.

But there is not - and never was - any guarantee (in MySQL or any RDBMS) that auto_increment values will always be sequential without gaps. The purpose of auto_increment values is - and ONLY is - to ensure a unique identifcation of each row. The value itself has no importance.

share|improve this answer

Your Answer


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.