« Many Plugins/Hacks
Preload Images »

Reset autoincrement value in mysql tabl

So tired of searching for this, (I know it’s simple, but for some reason I can’t seem to keep it in memory. LOL!)

You just deleted all the rows in a table - but when you add new rows - they’re still starting where the deleted ones left off.

Run this sql query to reset the autoincrement value:

TRUNCATE TABLE (TABLENAME)

This entry was posted on Thursday, June 24th, 2004 at 9:57 pm and is filed under mySQL. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

5 Responses to “Reset autoincrement value in mysql tabl”

  1. Scott Says:
    June 25th, 2004 at 3:18 am

    Of course TRUNCATE will also delete all your rows if you haven’t already. Not good if you don’t really want an empty table.

    If you just want to change the auto_increment without deleting everything, you can

    ALTER TABLE tbl_name AUTO_INCREMENT = xxx

    (where xxx is an integer). Or use the operations page in phpmyadmin (which is what I usually do so I don’t have to remember any SQL syntax :-)

  2. Daynah Says:
    June 25th, 2004 at 1:23 pm

    Thanks for the tip Jenn and Scott!

  3. Johanka Says:
    June 25th, 2004 at 4:58 pm

    Thanks! I wish I had known this earlier.
    I wonder, is it possible to edit the ID of several entries in the table en mass? Without having to edit manually one at a time?

  4. Scott Porter Says:
    July 1st, 2004 at 6:53 pm

    Yeah, it is possible to edit multiple id’s if you use phpMyAdmin (have just done so) Select the checkboxes of the rows to be changed, and click on the edit icon at the bottom, just after the last row :)

  5. Don't Back Down Says:
    January 6th, 2005 at 7:37 am Updating Auto-Increment in MySQL
    I was playing with the database yesterday, and decided that some of my auto-incremented values (id numbers for particular tables, specifically Movable Type’s PluginData) were simply getting too high. I was curious about resetting them. Even if I delet…