Howto reset AUTO_INCREMENT from a table in MySQL/MariaDB Databases

In this post, I’ll show you how to reset the auto_increment field from a table in MySQL/MariaDB databases. Sometimes you need to drop all the data in an existing table and reset the auto_increment to start from 1.

This post is dealing with only resetting the auto_increment to 1.

To reset the auto_increment, you need to know  the table name you’ll run the following command against. Connect to your database server and select the database that contains the table.

For example. I want to reset the auto_increment  from a table called finance, Run the following command:

ALTER TABLE finance AUTO_INCREMENT = 1;

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index.

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.

I hope this article is good enough for you.

See you in other articles.