Search code examples
mysqlauto-increment

What value can I set MySQL AUTO_INCREMENT to in order to reset it to the next available value

I have a table with indexed IDs like:

ID Name
1  abc
2  def
4  ghi
9  jkl
10 mno
11 xyz

The AUTO_INCREMENT value is siting at 12 in this example.

If I delete the 2 rows with IDs 10 and 11, would ALTER TABLEtable_nameAUTO_INCREMENT=1 reset the AI value to 10 or would it try to set it to 3 and thus break my table?

I am trying to set a standard AI reset that sets the value to the next available sequence in all cases.

Thanks for confirming the right way to set the query statement.

Solution

  • ALTER TABLE table_name AUTO_INCREMENT=1 did what I was hoping it would do.