MySQL Reset Auto Increment Values

In MySQL, the AUTO_INCREMENT attribute generates unique values for a column, typically the primary key. However, after deleting rows or truncating the table, you may need to reset the auto-increment value.

1. Reset AUTO_INCREMENT Using ALTER TABLE

You can manually reset the AUTO_INCREMENT counter to start from a specific value.

Syntax:

ALTER TABLE table_name AUTO_INCREMENT = new_value;

Example: Reset to 1

ALTER TABLE employees AUTO_INCREMENT = 1;

✅ This resets the next inserted ID to 1, as long as no higher values exist in the column.

2. Reset AUTO_INCREMENT After Deleting Data

When you delete rows, MySQL does not automatically reset the AUTO_INCREMENT counter.

Example:

DELETE FROM employees;
ALTER TABLE employees AUTO_INCREMENT = 1;

🚨 Important:

If any existing row has a higher id, the next inserted row will continue from the highest ID.
If the table is empty, the counter resets correctly.

3. Reset AUTO_INCREMENT Using TRUNCATE TABLE

A faster way to reset auto-increment is TRUNCATE TABLE, which removes all rows and resets AUTO_INCREMENT.

TRUNCATE TABLE employees;

✅ Best for completely clearing a table and resetting IDs.

🚨 Warning: This deletes all data permanently.

4. When to Reset AUTO_INCREMENT?

✅ After testing data in a table and needing a fresh start
✅ When you have deleted all rows and want IDs to start from 1
✅ If AUTO_INCREMENT values have grown too large unnecessarily

5. Conclusion

Use ALTER TABLE ... AUTO_INCREMENT = X to set a specific starting point.
Use DELETE + ALTER TABLE if you want to keep table structure but remove rows.
Use TRUNCATE TABLE to fully reset both data and AUTO_INCREMENT values.

🚀 Choose the best method based on your needs!

Souy Soeng

MySQL Reset Auto Increment Values