How To Reset MySQL Autoincrement Column

mysql-logo

MySQL database provides a wonderful feature of Autoincrement Column index. Your database table can define its primary key as Autoincrement number and MySQL will take care of its unique value while inserting new rows.

Each time you add a new row, MySQL increments the value automatically and persist it to table. But sometime you may want to reset the Autoincrement column value to 1. Say you writing a sample application and you have inserted few rows already in the table. Now you want to delete these rows and reset the autoincrement column to 1 so that new row which you insert will have primary key value 1.

There are few methods to achieve this.

1. Directly Reset Autoincrement Value

Alter table syntax provides a way to reset autoincrement column. Take a look at following example.

ALTER TABLE table_name AUTO_INCREMENT = 1;
Code language: SQL (Structured Query Language) (sql)

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

2. Truncate Table

Truncate table automatically reset the Autoincrement values to 0.

TRUNCATE TABLE table_name;
Code language: SQL (Structured Query Language) (sql)

Use this with caution. When Truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.

Once TRUNCATE is fired, the table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

3. Drop & Recreate Table

This is another way of reseting autoincrement index. Although not very desirable.

DROP TABLE table_name; CREATE TABLE table_name { ... };
Code language: SQL (Structured Query Language) (sql)

All these techniques are value techniques to reset autoincrement column number. Use whatever suits your requirement.

Disclaimer: The above commands can delete all your data! Be very very cautious.

Get our Articles via Email. Enter your email address.

You may also like...

30 Comments

  1. Shairyar says:

    Just curious to know if we can start the auto increment from the number that we desire rather than from 1

    • Yes you can do so. Specify whatever number you want to start the autoincrement columns value with in below query:

      ALTER TABLE table_name AUTO_INCREMENT = <NUM>;
      

      • saurabh says:

        how can i set a autoincreament value is =001 in mysql table…..and it will starts like 001, 002 , 003

        • xjshiya says:

          You can use the attributes unsigned zero fill and set the length of the integer. For example, you’ve set the integer length up to 5, then you also set the attribute unsigned zero fill, your auto-increment will look like this: 00001, 00002, 00003 and so on so forth. You can easily create one using phpMyAdmin or with this query: CREATE TABLE `db_name`.`tbl_name` (`col_name` INT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY);

      • Bhai says:

        Can U please help me, how do I Auto Re-arrange the AUTO_INCREMENT column after I delete a row from the table..

        • yasir says:

          ALTER TABLE record AUTO_INCREMENT = 1

          • ilwin says:

            i tried this but if i deleted a row of id=5 and my last insert row was of an id=8
            This method does not work at all it just remains as it is.
            It does not rearrange at all.
            So i see no point in using this if i have deleted a row from middle of my table.

  2. mittal says:

    it just work for my prob,,, thanks a ton

  3. krachleur says:

    tnx a lot

  4. Jeff says:

    So for a mysql table using innodb as the engine, is there a way to reset an autoincremented id back to 1.

  5. Joe says:

    Very helpful, thanks for the info!

  6. venki says:

    nice work

  7. Walter Freire says:

    Nice menu ! Would you send me the CSS and HTML codes ?

    Best regards,

    Walter.

  8. Very helpful. Thanks for posting this information.

  9. kalai says:

    how it is work..

  10. Mhong says:

    thanks a lot.

  11. Rafael says:

    I m using Toad for MySQl freeware 6.7 and anyone table of my schema doesnt Works..

    When i click in a table –> select to console Toad keep wait()….and i have to kill the process.

    i verifyed that in tab host i have a message:

    MySQL Table.Host does’t exitt. could be that?

  12. guru says:

    Thank you .. Very useful to me…

  13. 1 says:

    .net prblem

  14. Suresh Manikandan says:

    Thank you …

  15. Nagaraju says:

    Hello to Everyone, How To increment these Alphanumeric value
    ABCDEFYY(Current year 14)YY(nextyear 15)00001

    the result will appear like ABCDEF141500001
    ABCDEF141500002…..and so on
    plzzz help me…

  16. dinar qurbanov says:

    hi
    i have adde new rows with higher than regular autoincrement ID , ie it was near at 300 but i set it to 10000 to make test rows, that was mistake, and i wanted to know , whether i can use the IDs between 300 and 10000 . table is innodb. this article says “For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed” – and i have tested it with a test innodb table. and i have found out that even if i set the current incremental index to small value that is not used yet, if i add a new row without filling the auto increment id row in pma, it adds that to the index with the maximum use index+1 value. so even if innodb changes that without errors, it does not continue to work/function that way.

    • dinar qurbanov says:

      i have read the text incorrectly , i did not notice that it says “and the current sequence value is not changed”

  17. Shubham B says:

    Dude 1st method dosent work at all,it keep on incrementing the prevoiusly saved value.
    There is no question of using 2nd and 3rd Method when you already have heaps of data in your database.

  18. Vivek Moyal says:

    Is it possible to change the autoincrement when we have some rows starting from 1 and rightnow new entry will be 98 but i want that new entry will be 2501

  19. Tapa says:

    Thanks so much! The first method worked for me – didn’t have to try the other two.

  20. dp says:

    Hi I am getting error

    odbc sql server driver violation of primary key constraint pk_tablename can not inser duplicate key in object dbo.tablename. the duplicate key value is (GI2, R-10000 )

  21. Safi says:

    Thanks

  22. anonim says:

    thanks

  23. Munib says:

    Thanks alot for zerofill

Leave a Reply

Your email address will not be published. Required fields are marked *