Reseting the auto-increment number in a MySQL database using PHP

Question

I have a client where their next auto-increment number just jumped from 2300 to ********** for reasons not understood. They want it set back.

Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing. So, is there a way (programmatically) to set the next number in an auto-increment?

Something like:

alter table abc auto_increment = 2301;

Any ideas of why this happened?

Answer 1

You can try this: *ALTER TABLE tbl AUTO_INCREMENT = 100;
Source: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Answer 2

holes in sequence of auto increment happen using transaction for insert new record but you don't commit transaction itself it seems that the autoincrement is incremented anyway at least this is my case.

Answer 3

AUTO_INCREMENT => ONE OF THE COOL FEATURE

Suppose you have postid in your post table. AND you want that whenever anyone submit a post the postid gets incremented by one automatically.(without the user)

USER JUST SUBMIT THE POST or OTHER COLUMN FIELD . But postid is incrementing by itself whenever anyone enters. You can implement it on a primary key column.

---

Resenting the auto_increment :

#alter table abc auto_increment = 2301; 
#TRUNCATE TABLE table_name

(WARNING Truncate command will remove all the data from the table)

Go to table=>Operation=>Enter Auto_Increment value