12

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?

Palec
  • 12,743
  • 8
  • 69
  • 138
Drew
  • 836
  • 4
  • 12
  • 24
  • 4
    This is entirely the wrong way to go about handling deleted images. Just select the rows you need and loop through those. – Sam Dufel Jul 16 '11 at 16:25
  • 1
    Why would you want to do that? It also means you have to update all links to those images to make sure you still get the correct images. – Arjan Jul 16 '11 at 16:28
  • 1
    As far as i know you can't do that if you are using auto increment anyway. – Matt Jul 16 '11 at 16:29
  • @Sam Dufel can I for example only select rows 1 - 20 and loops through them? how would I do that? – Drew Jul 16 '11 at 18:17
  • as i do not have another table which uses these auto_incremented ids I found this to be helpful as well: 'ALTER TABLE mytable DROP id; ALTER TABLE upload ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1; ' – Drew Jul 17 '11 at 13:07

4 Answers4

22

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.

Drew
  • 836
  • 4
  • 12
  • 24
  • 2
    Just awesome, plain and simple solution! Mind explaining why and how it works? What does `FIRST` do? Oh and I would recommend changing `INT` to `INT UNSIGNED`... – Domi May 21 '15 at 09:37
  • perfect example of 'Sometimes such easy solutions work best that its hard to believe'. Thanks @Drew :D – Mihir Bhende Apr 06 '18 at 20:55
  • @Domi FIRST puts the column in the first position of the table. – mikl Mar 09 '19 at 12:43
12

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.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • This is a good approach. It's safer than the accepted solution with live tables that may have other activity adding to your accumulating table. In either case, however, it would be prudent to wrap these in a non-interruptable transaction, if there is much other activity. And for this solution, RENAME oldtable archivedtable; would be better than DROP. You could drop it once you're sure it all worked. – Mark Goldfain Mar 27 '17 at 02:45
2

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

Palec
  • 12,743
  • 8
  • 69
  • 138
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.

Johan
  • 74,508
  • 24
  • 191
  • 319
Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
  • dually noted. How would I loop the records from the table? – Drew Jul 16 '11 at 17:18
  • ahh ok this should do the trick SELECT * FROM tableName WHERE fieldName > _the_previous_id_ ORDER BY fieldName ASC LIMIT 1 is _the_previous_id_ is that a constant or would i replace that with my variable $id? – Drew Jul 16 '11 at 18:09