Set MySQL Auto Increment Manually

ในกรณีที่ต้องการกำหนดค่า 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/

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz