Home » Database » How to reset auto increment number in MySQL table

How to reset auto increment number in MySQL table



In MySQL tables auto increment keys are normally used to relate multiple tables to each other so that the data existing in multiple tables can be logically presented. In most of the cases, the auto increment field is, as the name suggests, automatically generated. Normally what happens is, as you enter a new row the value of the auto increment field is implemented to the next highest number automatically. For example, if the highest value that your auto increment field contains is 483, the next value is going to be 484.

It is advised that you don’t play around with the auto increment field or key unless it is absolutely necessary. Since this key is used to relate various tables, one mishap, and your entire database schema may collapse. So be very sure that you really want to reset your auto increment field number. Anyway, why would you reset the auto increment number in a MySQL table?

Suppose you needed to delete a particular set of records and a particular number of keys in this particular table are already being used by another table. So if you delete data in this table and re-enter it, your auto increment number will be starting from the highest number that existed prior to the deletion. You don’t want that. You want the number to again start from the actual sequence. Difficult to understand? Let us go through a numeric example.

Suppose there is a table called “mtable”. The highest auto increment value in the key field is 360. You enter some records and by the time you’re done, the highest value in the auto increment key is now 483. You have related these keys to another table. Then you realize there was some mistake in the data you entered and you need to delete all the data you had just entered. The problem with this is, when you enter the data again after deleting it, the highest value in the auto increment field is going to be 483 and not the original 360. Since you’re basically entering the same data with some changes, you would like the auto increment counting to begin from 361 onwards and not 484. For this, you need to reset your auto increment value in your MySQL table, and this is how you do it

ALTER TABLE mtable AUTO_INCREMENT=360

Now when you again start entering the old data, the auto increment will start from 361 and not 484.