insert random number into table

Hello All,
I am trying to insert a unique random number* with each row when inserting
into a table. I can do it in the application layer (php) but just wondering if
there is an easier way in the database layer.
Thanks
Ray

* for example, when registering a person for a contest, I want to give them a
unique, random identifier.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: insert random number into table

There is a RAND function in MySQL, but if you need to guarantee that the
identifiers are unique you should consider using an auto-increment field.

If that isn't unique enough, you can use the UUID or UUID_SHORT functions.
Theoretically, those should return values that are unique across the Internet
(at least).

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


>-----Original Message-----
>From: Ray [mailto:ray@stilltech.net]
>Sent: Friday, October 16, 2009 11:43 AM
>To: mysql@lists.mysql.com
>Subject: insert random number into table
>
>Hello All,
>I am trying to insert a unique random number* with each row when inserting
>into a table. I can do it in the application layer (php) but just wondering
>if
>there is an easier way in the database layer.
>Thanks
>Ray
>
>* for example, when registering a person for a contest, I want to give them a
>unique, random identifier.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: insert random number into table

On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
> There is a RAND function in MySQL, but if you need to guarantee that the
> identifiers are unique you should consider using an auto-increment field.
>
> If that isn't unique enough, you can use the UUID or UUID_SHORT functions.
> Theoretically, those should return values that are unique across the
> Internet (at least).
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
> >-----Original Message-----
> >From: Ray [mailto:ray@stilltech.net]
> >Sent: Friday, October 16, 2009 11:43 AM
> >To: mysql@lists.mysql.com
> >Subject: insert random number into table
> >
> >Hello All,
> >I am trying to insert a unique random number* with each row when inserting
> >into a table. I can do it in the application layer (php) but just
> > wondering if
> >there is an easier way in the database layer.
> >Thanks
> >Ray
> >
> >* for example, when registering a person for a contest, I want to give
> > them a unique, random identifier.
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> >infoshop.com


Thanks for the replies. I think I'll use the php solution as it is the better
match for what I am trying to do. I'l keep the UUID functions in mind for
future use though.
Ray

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: insert random number into table

Don't try to give them a random number, instead use a table with a primary =
key that is AUTO_INCREMENT. Then you just insert the record, and afterward=
s SELECT LAST_INSERT_ID(); to retrieve the id for the record created.

With random numbers, you're going to have more collisions when you add more=
records.

Regards,
Gavin Towey

-----Original Message-----
From: Ray [mailto:ray@stilltech.net]
Sent: Friday, October 16, 2009 8:43 AM
To: mysql@lists.mysql.com
Subject: insert random number into table

Hello All,
I am trying to insert a unique random number* with each row when inserting
into a table. I can do it in the application layer (php) but just wondering=
if
there is an easier way in the database layer.
Thanks
Ray

* for example, when registering a person for a contest, I want to give them=
a
unique, random identifier.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: insert random number into table

>-----Original Message-----
>From: Ray [mailto:ray@stilltech.net]
>Sent: Friday, October 16, 2009 1:10 PM
>To: mysql@lists.mysql.com
>Subject: Re: insert random number into table
>
>On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
>> There is a RAND function in MySQL, but if you need to guarantee that the
>> identifiers are unique you should consider using an auto-increment field.
>>
>> If that isn't unique enough, you can use the UUID or UUID_SHORT functions.
>> Theoretically, those should return values that are unique across the
>> Internet (at least).
>>
>> Regards,
>>
>> Jerry Schwartz
>> The Infoshop by Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> www.the-infoshop.com
>>
>> >-----Original Message-----
>> >From: Ray [mailto:ray@stilltech.net]
>> >Sent: Friday, October 16, 2009 11:43 AM
>> >To: mysql@lists.mysql.com
>> >Subject: insert random number into table
>> >
>> >Hello All,
>> >I am trying to insert a unique random number* with each row when inserting
>> >into a table. I can do it in the application layer (php) but just
>> > wondering if
>> >there is an easier way in the database layer.
>> >Thanks
>> >Ray
>> >
>> >* for example, when registering a person for a contest, I want to give
>> > them a unique, random identifier.
>> >
>> >--
>> >MySQL General Mailing List
>> >For list archives: http://lists.mysql.com/mysql
>> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>> >infoshop.com
>
>
>Thanks for the replies. I think I'll use the php solution as it is the better
>match for what I am trying to do. I'l keep the UUID functions in mind for
>future use though.
>Ray
>
[JS] Just remember that the pseudo-random numbers that come out of a computer,
regardless of where or how they are generated, are not guaranteed to be
unique. Although the odds of a collision are slim, my philosophy has always
been "if it's possible, it will happen - in the middle of your vacation".

If you want to generate UUIDs in PHP, take a look at
http://www.shapeshifter.se/2008/09/29/uuid-generator-for-php /
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: insert random number into table

--Apple-Mail-1-385925382
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

In addition to what Gavin said. You seem to want some form of key
perhaps to be able to identify the authenticity of your contest
winner. An auto increment ID won't be very secure for that, but you
still need it.

Take a known combination of perhaps the key, name, email address, etc,
and run a hashing function on it. Md5 will often suffice.

This will give you a unique hash that only you know how it was made.
You can inject some 'salt' into a known location to add more
difficulty in guessing your hashing input.

This hash will be asking too much of the user to hand type in, so I'm
assuming you will accept it via a link in a URL.

Or ask for whatever you used to make the hash and rehash that and
compare your internal stored result.

If not, for your needs you may be able to safely remove ambiguous
characters, and take a substring of it to make it shorter.

It's also quite easy to create a dictionary of words and create a
function that can make word1-xx-word2 and end up with red-56-car.

--
Scott
Iphone says hello.

On Oct 16, 2009, at 11:25 AM, Gavin Towey wrote:

> Don't try to give them a random number, instead use a table with a
> primary key that is AUTO_INCREMENT. Then you just insert the
> record, and afterwards SELECT LAST_INSERT_ID(); to retrieve the id
> for the record created.
>
> With random numbers, you're going to have more collisions when you
> add more records.
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Ray [mailto:ray@stilltech.net]
> Sent: Friday, October 16, 2009 8:43 AM
> To: mysql@lists.mysql.com
> Subject: insert random number into table
>
> Hello All,
> I am trying to insert a unique random number* with each row when
> inserting
> into a table. I can do it in the application layer (php) but just
> wondering if
> there is an easier way in the database layer.
> Thanks
> Ray
>
> * for example, when registering a person for a contest, I want to
> give them a
> unique, random identifier.

--Apple-Mail-1-385925382--

Re: insert random number into table

I always maintain a timestamp in my random numbers. As long as my
precision is higher than my requests per second, wouldn't I be safe
from collisions? Assuming a time machine is not invented.

--
Scott
Iphone says hello.

On Oct 16, 2009, at 11:29 AM, "Jerry Schwartz" > wrote:

> JS] Just remember that the pseudo-random numbers that come out of a
> computer,
> regardless of where or how they are generated, are not guaranteed to
> be
> unique. Although the odds of a collision are slim, my philosophy has
> always
> been "if it's possible, it will happen - in the middle of your
> vacation".

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: insert random number into table

On October 16, 2009 12:29:42 pm Jerry Schwartz wrote:
> >-----Original Message-----
> >From: Ray [mailto:ray@stilltech.net]
> >Sent: Friday, October 16, 2009 1:10 PM
> >To: mysql@lists.mysql.com
> >Subject: Re: insert random number into table
> >
> >On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
> >> There is a RAND function in MySQL, but if you need to guarantee that the
> >> identifiers are unique you should consider using an auto-increment
> >> field.
> >>
> >> If that isn't unique enough, you can use the UUID or UUID_SHORT
> >> functions. Theoretically, those should return values that are unique
> >> across the Internet (at least).
> >>
> >> Regards,
> >>
> >> Jerry Schwartz
> >> The Infoshop by Global Information Incorporated
> >> 195 Farmington Ave.
> >> Farmington, CT 06032
> >>
> >> 860.674.8796 / FAX: 860.674.8341
> >>
> >> www.the-infoshop.com
> >>
> >> >-----Original Message-----
> >> >From: Ray [mailto:ray@stilltech.net]
> >> >Sent: Friday, October 16, 2009 11:43 AM
> >> >To: mysql@lists.mysql.com
> >> >Subject: insert random number into table
> >> >
> >> >Hello All,
> >> >I am trying to insert a unique random number* with each row when
> >> > inserting into a table. I can do it in the application layer (php) but
> >> > just wondering if
> >> >there is an easier way in the database layer.
> >> >Thanks
> >> >Ray
> >> >
> >> >* for example, when registering a person for a contest, I want to give
> >> > them a unique, random identifier.
> >> >
> >> >--
> >> >MySQL General Mailing List
> >> >For list archives: http://lists.mysql.com/mysql
> >> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> >> >infoshop.com
> >
> >Thanks for the replies. I think I'll use the php solution as it is the
> > better match for what I am trying to do. I'l keep the UUID functions in
> > mind for future use though.
> >Ray
>
> [JS] Just remember that the pseudo-random numbers that come out of a
> computer, regardless of where or how they are generated, are not guaranteed
> to be unique. Although the odds of a collision are slim, my philosophy has
> always been "if it's possible, it will happen - in the middle of your
> vacation".
>
> If you want to generate UUIDs in PHP, take a look at
> http://www.shapeshifter.se/2008/09/29/uuid-generator-for-php /
>
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> >infoshop.com


Thanks for all the suggestions and points. There are definitely some I will
keep in mind for future projects. auto increment won't work because I don't
want the number to be based on the order of assignment.
I probably should have been more clear. What I am trying to do is randomly
assign one of a fixed pool of numbers uniquely to each row.
What I ended up doing is (in PHP):
SELECT all used numbers and put them in an array.
create an array of all allowed numbers.
combine the two arrays such that all available numbers are left in a third
array with index numbers from 1 counting up sequentially. I then randomly
choose one of those numbers and write it to the table.
Thanks again for all your suggestions.
Ray

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: insert random number into table

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com

>-----Original Message-----
>From: Scott Haneda [mailto:talklists@newgeo.com]
>Sent: Friday, October 16, 2009 2:44 PM
>To: Jerry Schwartz
>Cc: Ray;
>Subject: Re: insert random number into table
>
>I always maintain a timestamp in my random numbers. As long as my
>precision is higher than my requests per second, wouldn't I be safe
>from collisions? Assuming a time machine is not invented.
>
[JS] "As long as..." is very dangerous. Even if your mean transaction arrival
rate is okay, you can't rule out the possibility of two transactions arriving
at the same time (within the precision of your clock). Admittedly, we're
getting theoretical here; but I always like to come at things from the
theoretical side, since it is so easy to overlook your assumptions if you come
at it from the opposite direction.

>--
>Scott
>Iphone says hello.
>
>On Oct 16, 2009, at 11:29 AM, "Jerry Schwartz" > > wrote:
>
>> JS] Just remember that the pseudo-random numbers that come out of a
>> computer,
>> regardless of where or how they are generated, are not guaranteed to
>> be
>> unique. Although the odds of a collision are slim, my philosophy has
>> always
>> been "if it's possible, it will happen - in the middle of your
>> vacation".




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org