MySQL allows various ways to reset the auto-increment column value. Sometimes, we need to delete these rows and reset the auto-increment column so that when we do the next insertion into a table, the first record's identity will have primary key value 1. Next, if we add a new row without giving the value to the auto-increment column, MySQL automatically adds a new row with id=11. Each time we will insert new records into a table, MySQL increments the value automatically to the auto-increment column.įor example, we have a table that already contains ten rows. Generally, we use the auto-increment attribute for the primary key field in a table.

It always generates a unique identity for each newly inserted row into the table. This work by Alessandro Cuzzocrea is licensed under a Creative Commons Attribution 4.0 International License.Auto-increment is a useful feature provided by MySQL. In both cases, attempting to set a lower auto-increment value does not generate an error. If you try to reset the auto-increment value to a number less than the current maximum value in the column, no error occurs, but the current sequence value is not changed.If you try to reset the auto-increment value to a number less than or equal to the current maximum value in the AUTO_INCREMENT column, MySQL will reset it to the current maximum value plus one.

The behavior of resetting the auto-increment value to a lower number depends on the storage engine used: Source Code How to Reset MySQL Auto-Increment Valuesġ- Find the current auto-increment value: SELECT `AUTO_INCREMENT `Ģ- Set a new auto-increment value: ALTER TABLE Notes

Reset mysql quot increment