How to re-assign AUTO_INCREMENT column for every r

Question

I have an image gallery which website members can upload images to. When an image is uploaded, a MySQL row is written, containing various pieces of information about the image, member, etc. This row uses AUTO_INCREMENT to create its ID, so that getimage.php?id=XX can fetch the image to be displayed.

I loop through the IDs with a for-loop to display the images within the gallery.

If I delete the 5th row/image, for example, the AUTO_INCREMENT goes from 123456789 to 12346789.

I would like to re-assign the ID to each row in the MySQL table, starting from the ground up. So 12346789 becomes 12345678. How would I achieve this?

Answer 1

As other already stated, this is the wrong approach, but in case you need renumbering (it's valid for sort column for example), here is the query:

UPDATE tabaleName SET fieldName = fieldName - 1 WHERE fieldName > 5 ORDER BY fieldName ASC

And since you are using auto-increment, you have to reset it

ALTER TABLE tableName AUTO_INCREMENT=10000

But please, note, I post this for education purposes only. This is the wrong approach! Instead of providing the exact ID in the query when you click next/prev, do

SELECT * FROM tableName WHERE fieldName > _the_previous_id_ 
ORDER BY fieldName ASC LIMIT 1

Or even better, select all records in the album, and loop them.

Answer 2

I found this to work perfectly and quite quickly so here it is:

ALTER TABLE tablename DROP id

ALTER TABLE tablename ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1

I know this isn't the proper approach however for my specific situation this is exactly what was needed. There is nothing within the table I was using that is referred to either from or to another table.

Answer 3

Using user-defined variables:

SET @id = 0;
UPDATE table SET id = @id := @id + 1 ORDER BY id;
SET @alt = CONCAT('ALTER TABLE table AUTO_INCREMENT = ', @id + 1);
PREPARE aifix FROM @alt;
EXECUTE aifix;
DEALLOCATE PREPARE aifix;

Example use

• http://www.paulwhippconsulting.com.au/webdevelopment/31-renumbering-an-qorderingq-field-in-mysql
• http://www.it-iss.com/mysql/mysql-renumber-field-values/

Answer 4

If you want the auto-incrementing id's to always be consecutive, let it go, it's a useless struggle.

If you just want to renumber once, that's easy:

Create a new table with the same layout:

CREATE TABLE mytable2 LIKE oldtable;

Copy all rows to the new table, remember to not select the auto_incrementing id, otherwise the old numbering will be retained.

INSERT INTO mytable2 (field2, field3, field4)
  SELECT field2, field3, field4 FROM oldtable ORDER BY oldtable.id;

RENAME oldtable archive;

RENAME mytable2 oldtable;

DROP archive;

You now have consecutive numbering.