Re-sorting id column in a MySQL table?

I have a table with the column name id and the settings of both PRIMARY KEY and AUTO INCREMENT. When a row is deleted mid-way through the table, i.e. row 29 in rows 1 - 70, id 29 will simply disappear. Is it possible to have it so that the rows shift up, but the id remains there, i.e. deleting 70 instead of 29, so that the row that had id开发者_运维问答 30 will now adopt 29, 31 will adopt 30 etc?

This will make it a lot easier when inserting new data into the table in my case if this is possible.

I'm really looking to have the values used up before a new one is created, so it's either this which would be great, or looking for missing values and updating them.

UPDATE:

I wanted this to say, get the row above a certain row, and if all the id columns were in straight value order (no gaps) then I'd be able to do it relatively easily.

In general, it's a hassle to update primary keys in a database since this often sets of a chain of updates through all the tables which reference that primary key. Many engines allow you to force this update to happen automatically using the CASCADE option but it still results in a lot of otherwise unnecessary database updating.

The normal use of auto-incrementing integer primary keys is as permanent, meaningless, immutable values.

That said, if you really want to update those keys, you could do so with the command

 UPDATE YourTable SET id = id - 1 WHERE id > (value you deleted)

Alternatively, you can maintain a second integer column, not the primary key of the table, and update this column after each deletion.

answer to the edited question

Previous ID:

SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1; 

Next ID:

SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;

This will make it a lot easier when inserting new data into the table in my case if this is possible.

It shouldn't make any difference in your inserts. The column is an auto increment, so it's an unspecified column in your inserts. How does a gap at 29 make it any more or less difficult to insert your 1000th record?

On top of that, if you're re-assigning values to the column that is your primary key, you lose all referential integrity for any tables that reference that column.

To answer your question directly, you can simply drop the auto increment column and recreate it. It'll re-start the numbering at 1 and remove the gaps. However, I strongly recommend against doing this since it's completely unnecessary.

Auto-increment primary keys need to be unique, but they don't need to be contiguous.

You're trying to solve a problem that does not need to be solved. Gaps in auto-increment primary keys are okay. You don't need them to be contiguous. Trying to make them contiguous causes more problems:

• It's costly to run queries to find gaps.
• It's costly to update lots of rows to shift primary key values to fill the gap. What if you have 10 million rows, and there's a gap after row 2?
• It's likely that you'll get a new INSERT while you're shifting values. Now you have to shift again.
• If you shift rows to fill the gap, don't forget to ALTER TABLE to change the next auto-increment value that will be generated. Now you have to run two UPDATEs and a table-locking ALTER after every DELETE, which is terrible for scalability.
• If you re-use primary key values, you'll confuse your application. It may have deleted a row for a reason. If an application searches for a value based on the primary key, it should find that the row was deleted. It should not find some other arbitrary row that has been shifted into that place.

If you need primary keys to be contiguous, then you're using them as a de facto COUNT, or else some kind of ranking. This is not what the auto-increment mechanism is designed to do.