Reset Auto Increment Value

Problem

Auto increment values are always incrementing, as the name already indicates. It's usually a good thing to keep that to ensure you don't run into conflicts with existing data, but sometimes, maybe while you're working in a development environment, you want to keep your tables small to have a good overview and not get lost in millions of rows.

You can easily remove all rows using DELETE FROM your_table, but that will not reset the auto increment value.


Solution

If you already ran your DELETE FROM query, you can easily set the next auto increment value using this query:

ALTER TABLE your_table AUTO_INCREMENT=1;

If you're clearing the whole table, you can also run this query using the DDL command TRUNCATE:

TRUNCATE TABLE your_table

This will remove the data from your table and also reset the auto increment value.

Comments


Please sign-in to post a comment