reset auto increment integer in mysql

Because I use auto-increment integers in almost every table I can for the primary key, they tend to get very large very fast. This can sometimes be an annoying problem when I’m debugging an application or testing a mysql database query. They are resetable and it’s so much quicker to select from the database when you’re checking for id=3 or id=25 then id=305678.

The query to reset the auto increment primary id of your choosing is :


ALTER TABLE some_table AUTO_INCREMENT = 1;

It is also possible to set the next auto_increment number
You can do it like this :


SET insert_id=5;

Now your next mysql_insert_id() will be 5.

Share

Tags: , , , ,

This entry was posted on Thursday, March 19th, 2009 at and is filed under . You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

4 Responses to “reset auto increment integer in mysql”

  • Brian Says:
    March 20th, 2009 at

    What happens when you set the insert_id to an id that is already being used?

  • admin Says:
    March 20th, 2009 at

    If you try to set the insert_id to an integer value that is already taken then you will get an error when you try the insert.
    I just tried it and got :

    
Error Code : 1062
Duplicate entry '105' for key 1

  • Brian Says:
    March 20th, 2009 at

    What if you have primary ids 3,4,5 free but not 6,7,8. If you set the mysql insert_id to 3 and insert 4 records will it continue along 3,4,5 and then bypass 6,7,8 or will the fourth database query return an error?

  • admin Says:
    March 20th, 2009 at

    SET insert_id
    will only work for one insert into the database. If you want to fill 3,4,5 you will have to set the insert_id before each insert. If you don’t the the mysql auto increment integer will just go back to wherever it was before.