CODEWITHSUNDEEP
mysql
freddyzcount90
freddyzcount90

Reputation: 69

How to "reset" id in MySQL table

I've made a database with users and each user has an id that is autoincremented. But when I delete a user from the table, the id doesn't "reset":

enter image description here

How can I fix this? Thanks

Upvotes: 6

Views: 9968

Answers (3)

Aslan Kayardi
Aslan Kayardi

Reputation: 453

You need to run a UPDATE Query.

SET @reset = 0; UPDATE YOUR_TABLE_NAME SET id = @reset:= @reset + 1;

Upvotes: 23

Joni
Joni

Reputation: 111269

You can reset the auto increment counter to the next available value with:

alter table MYTABLE auto_increment = 0;

See also https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You don't fix this. The database is doing exactly what it is designed to do. As the first line in the documentation on AUTO_INCREMENT says:

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows . . .

Note: Nothing about sequentiality or not having gaps.

The auto-incremented value is designed to uniquely identify each row (among other things). Once a row is identified, that doesn't change. This identifier could be used by other tables (foreign key references). This identifier could be written down by someone to remember that id=378 had something special.

The key idea is that the value does not change over time.

There is no problem having gaps in such identifiers. If you want to

Upvotes: 1

Related Questions