Reset id values in existing table (without deleting records)

Question

let me say that I have a table in my phpmyadmin that looks like this:

id | name
1  | John
2  | Dave
5  | Tiffany

As U can see by id - i've deleted 2 records between 'Dave' and 'Tiffany'. My question:

Is there a way to 'reset' or repopulate the id so that the 'Tiffany' record would have id=3 and so on ?

The only way to start counting id again I've found is a 'TRUNCATE' but it deletes all my records which I dont want

You can check this question [here](http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key). Very beneficial — Osama Sayed, Jun 20 '16 at 11:39
Don't do that... No point at all, will only mess up foreign keys. — jarlh, Jun 20 '16 at 11:39
Why is it important to you that there are 'gaps' in the id's? The database doesn't care. Are using using the id's for people to see? if so, please don't do that. imo, users should never see a raw database record id. Why? it is meaningless to them. — Ryan Vincent, Jun 20 '16 at 13:28

score 0 · Answer 1 · answered Oct 28 '16 at 21:47

I do this sometimes when I create a new table and want to get 'clean' ids. If there is no reference to / connection with other table, you can do:

SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT = 1;

Do not do this for ids already used in other tables!

1 Answers1