CODEWITHSUNDEEP
mysqlauto-increment
Abdallah Qaraeen
Abdallah Qaraeen

Reputation: 113

Unable to reset AUTO_INCREMENT in MySQL?

I have a table in MySQL that has 997653 rows, but after I have deleted a few, it is now 994332 rows. However, the last number of the ID column is still 997653. How can I reset it to recount the columns?

I have tried ALTER TABLE foo AUTO_INCREMENT=1; but that did not work.

Upvotes: 0

Views: 2342

Answers (2)

axiac
axiac

Reputation: 72376

If the table engine is InnoDB then it's enough to restart the MySQL service (if this is possible on your setup).

The InnoDB table engine doesn't store the auto-increment value anywhere. When the server starts, it finds the largest value present in the table (it's easy, the AUTO_INCREMENT column is always the table's PK), increments it and this is the new value it uses on the next INSERT.

It is explained in the documentation:

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1.

Upvotes: 1

Rahul
Rahul

Reputation: 77926

That's the right syntax for reseeding but you should never reseed the auto_increment column value. Yes once you delete rows the auto_increment value will keep going for next value but that doesn't cause any effect to your table or queries made on that table.

if matter, you can even consider performing a soft delete instead doing a hard delete issuing delete from... statement

Upvotes: 1

Related Questions