How to fix SQL indexes to remove spaces/jumps?

I have a table where entries are listed and sometimes edited and removed开发者_运维技巧 which creates a space. Example: 1,2,4,5,6,8,10 I would like to have them in order example: 1,2,3,4,5,6,7

Is there any code to do this qick and painless?

Thank you.

---

If you have any foreign key constraints then you'll need to use ON UPDATE CASCADE (something I tend to avoid like plague).

Is there a good reason to ensure these numbers are sequential or is just for aesthetics?

There's no problem having keys 'missing' - the database cares very little.

IF you really need to do this then:

SET @indx = 1;
UPDATE `table` SET `field` = (@indx:=@indx+1) ORDER BY `field` ASC;

Have fun...