auto_increment reorder

[dalar]

I have tried searching for this problem, but nothing came up.

suppose I have the following mysql table

ID NAME
1 bob
2 dave
3 henry
4 steven
5 mary
6 john

the column ID, is set to auto increment and is the primary key obviously. Now say I delete steven and mary, then the total order of the IDs will be, 1 2 3 6 ... is there anyway I can get mysql to restack it so there are no missing numbers, so that the new order will rearange it self to read 1 2 3 4.

For my application, it is fine to reorder the ID field as no one will be bookmarking a link or anything, this is just for the script use.

I know in phpMyAdmin you can reset the auto index, but that wont really restack everything.

thanks in advance 🙂

[bogu]

I suggest u to let the autoincrement to work how is suppose to work, if u need an order field just add one...

[Houdini]

Auto_increment just adds one to the last number regardless of what you delete, but if you really want to reset then use

ALTER TABLE tablename AUTO_INCREMENT = 5

but you will be doing that alot if you delete members frequently. Those numbers are more for MySQL to maintain a unique number (or field) so there are no duplicates, so what that number is even though out of order to you MySQL doesn't even care.