Mysql:Reset Auto increment No.

I have a database table with a auto increment column for primary key. As the records being add and delete many times, the auto increment value will keep increasing.

Problem One:

I want to delete all data from table.

Solution:

TRUNCATE table tbl_name

this command will delete everything from the table and start again from 1.... DELETE just does the same but keeps the current auto_increment value....

Problem Two:
If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9.

Solution:
Run a query: ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto increment value to current largest value in the auto increment column + 1. So, the auto increment value of next inserted record will start from 9.

Problem Three:
If I have entered 10 records, and deleted center records - 4th, 5th. I want to insert next record as 4th not 11th.

Solution:
Run the following query:
SET insert_id = 4;
INSERT INTO tablename VALUES (’blah’, ‘…’);

This will add the next record into record 4th.

The SET insert_id = #(where # is the next auto increment value you want to use) will reset the next auto increament value, the next query(INSERT) you run will use your choice of value.