Fix or Reset MySQL AUTO_INCREMENT Value

Our goal in this post is to show you how to reset the MySQL AUTO_INCREMENT value (for single or all tables) by taking the highest value in the auto_increment column and resetting it to the next value.

For a single table

ALTER TABLE table_name_here AUTO_INCREMENT = 0

It's just that simple :)

For all (multiple) tables

To do that, we have multiple steps

Get the corresponding query for all tables

SELECT concat('ALTER TABLE ', 'your_db_name', '.', TABLE_NAME, ' AUTO_INCREMENT = 0;') as query
FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db_name';

Run the query above in PHPMyAdmin, and you will get something similar to the results below

Export and copy all the queries (for all tables)

1. Click on the "Show all" button

2. 

   Show full queries text

   1. Click on the "Extra options" button
   2. Check the "Full texts" radio button
   3. Click on the "Go" button

   

3. Click on the "Export" button at the bottom of the list

4. 

   Select "CSV" as the export format

5. 

   Then click on the "Export" button at the bottom of the page

6. Open the exported CSV file, and copy all queries rows that you want to run

Paste the queries

Now, got to the "SQL" tab

Then paste the queries you want, and click on the "Go" button

And VOILA!

Thanks for your time, and you share it with your developers' friends :)

References

• https://stackoverflow.com/a/3595783/19179327

• https://stackoverflow.com/a/45782174/19179327