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?

1 Answer 1


I think you will find the answer in MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

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.


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.