Fix following id's when a row is deleted

I have a table with some rows, each row has a unique key. When a row is deleted from the table, all rows that are below this row should be 'moved up'. Is there some built in function in MySQL that does this or should I just do it with PHP or perhaps UPDATE table SET id=id-1 WHERE id > deletedid?

Using the last one seems a bit messy. What would be the bes开发者_如何转开发t way to do this?

1. Why do you want to do this? I know it's ugly to have holes in your unique ID sequence, but the downside of invalidating any references to IDs from outside the database is normally very much greater. The normal thing is to just accept the sequence won't be contiguous. If these represent a sequence, consider just sorting by the order rather than expecting the N'th value to have value N (any sort of iteration should provide its own index somewhere for this use).

2. If the value is one you set yourself, and you definitely want to keep it as having values from 1 to N (N="number of rows"), and you want to keep the sequence of values even if they're not in the order the rows were inserted, then "UPDATE table SET id=id-1 WHERE id > deletedid" is probably the best answer.

3. If the value is an auto_increment field, and you don't care which numbers go with with rows as long as each row has a number from 1 to N, you can alternatively do ALTER TABLE DROP COLUMN 'columnname' and then ALTER TABLE again to add the column again, and the database will regenerate the ids from 0. (Not necessarily in the same order, though it often is.)

4. There may be a way to renumber only the rows after that point, but (according to a quick google) it doesn't look like there's anything easier than what you're already planning.

First you have to ensure that the column is not a foreign-key for any other table.

Then you can try this (I am not 100% positive it will work):

DELETE FROM 
  MyTable
WHERE 
  id = deletedid;

UPDATE 
  table
SET 
  id=id-1
WHERE 
  id > deletedid
ORDER BY 
  id

As stated in mysql docs:

If the ORDER BY clause is specified, the rows are updated in the order that is specified.

and in this way you ensure uniqueness of the field.