sqlmysqldatabase-schemadatabase-programming

Have mysql re-allocate ids in a continuous range

I have an existing database in mysql. One of my tables has discontinuous ids. I would like to modify the ids of the table so that they go from 1 to num-of-rows.

This particular tables does not happen to have incoming references, so the ids can be changed without modifying other tables.

The reason I want to do that is that I want to process the data with a tool I am writing and if the ids are continuous then many things will be simpler.

Solution 
  • SET @r := 0;
UPDATE  mytable
SET     id = (@r := @r + 1)