Reseting mysql autoincrement field value after delete from php?

Question

I have a form from which i am inserting data into mysql works fine.But when i delete some data from mysql, and inserted values into database again the autoincrement value is starting from the previous row value. ForExample:

If i have 1,2,3,4,5 as id's in mydatabse and if i delete 4 and 5 id's from database and started inserting next data from PHP. then the id's are coming from 6.... But i need to get id as 4 .can any one give suggestions.Thanks in advance.

Answer 1

I'm afraid MySQL does not allow you to "reset" AUTO_INCREMENT fields like that. If you need that behavior, you have to stop using AUTO_INCREMENT and generate your IDs manually.

Answer 2

Auto increment does not (and cannot) guarantee an unbroken sequence.

You can implement this yourself as "SELECT MAX(ID) + 1 FROM MYTABLE;"

But be warned: You will take a slight but noticeable performance hit.

1. If you are running updates concurrently you risk deadlocks
2. (again if you are running updates concurrently) you will risk having two inserts with the same key.

You can also implement this by running your own counter in a separate table. You must have program logic to decrement this correctly on a deletion, and, again you will get a performance hot and risk of deadlock as the "counter" will become an object of contention.

Answer 3

You should not play with AUTO_INCREMENT value in a production environment let MySQL take care of its value for you.

If you need to know how many row you have you can use

SELECT COUNT(id) FROM tbl;

Anyway if you really want to change its value the syntax is :

ALTER TABLE tbl AUTO_INCREMENT=101;