ในกรณีที่ต้องการกำหนดค่า auto increment ใหม่ สามารถทำได้ตามรายละเอีดข้างล่างนี้
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:
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 Two:
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.
Note: only effective for the immediate next query, one time.
Source: http://www.liewcf.com/archives/2004/04/mysql-reset-auto-increament-number/