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:talkli...@newgeo.com] Sent: Friday, October 16, 2009 2:44 PM To: Jerry Schwartz Cc: Ray; mysql@lists.mysql.com 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 jschwa...@the-infoshop.com 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=arch...@jab.org
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=arch...@jab.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:r...@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=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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:r...@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=jschwa...@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=arch...@jab.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 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:r...@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=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please 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=arch...@jab.org
RE: insert random number into table
-Original Message- From: Ray [mailto:r...@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:r...@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=jschwa...@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=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: insert random number into table
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 gto...@ffn.com 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:r...@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.
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 jschwa...@the-infoshop.com 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=arch...@jab.org
Re: insert random number into table
On October 16, 2009 12:29:42 pm Jerry Schwartz wrote: -Original Message- From: Ray [mailto:r...@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:r...@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=jschwa...@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=jschwa...@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=arch...@jab.org
