[thelist] MySQL queries and AUTO_INCREMENT

[Bernardo Escalona-Espinosa]

Thanks so far for the replies.

> After you finish your operations on that table, copy the table to
> another table (structure+data), drop the original table then copy back
> the table on the old name. You'll have your auto-incremented ID without
> gaps.
>
> Best,
> Dan

Dan,
I gave this a try and this is what happened: I deleted every entry
with ID higher than 134. Then I did the copy, drop, rename like you
suggested. Then I added new rows expecting them to appear with IDs
135, 136, etc. Instead, all the new rows have ID of zero (although ID
is the PK and supposed to be unique). What did I do wrong?


> Why do the gaps bother you? auto_increment is only a way to ensure you
> have a unique identifier. If you need something that has some semantics
> behind it, might I suggest another field, e.g.:
>
> id        int     auto_increment   pk
> foo       varchar(255)
> priority  int     not null
> ...
> Matt Warden

Matt,
I would like the IDs to be continuous because in another part of my
page i generate thumbnails of random photos. The randomly generated ID
is used to retrieve the corresponding thumbnail, so if the ID doesn't
exist because of a gap, I have a problem.

Thanks all so far for the help.

Berns


--
___________________________________________
Bernardo Escalona Espinosa
  handy: 0176 / 24 82 35 34
  http://www.bernsonline.com/