[thelist] MySQL queries and AUTO_INCREMENT

Ken Schaefer Ken at adOpenStatic.com
Fri Apr 7 06:12:44 CDT 2006


: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Bernardo Escalona-Espinosa
: Sent: Friday, 7 April 2006 8:01 PM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] MySQL queries and AUTO_INCREMENT
: 
: > 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.

Then you are completely mis-using the auto-increment as your primary key. PKs
are there to identify the row uniquely. They are not there to fall within a
range in some random number generator.

Can I *strongly* suggest that you do not attempt to use the auto-increment as
your primary key if that's also the field you are going to be renumbering for
purposes of your random record formula.

I suspect there is probably some other way you can get around this. I don't
really know mySQL well, but I have this page for Access/Jet and SQL Server:
http://www.adopenstatic.com/faq/randomrecord.asp

Neither of the two mechanisms relies on sequential PK values. Maybe something
similar could be used in your case?

Cheers
Ken



More information about the thelist mailing list