|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
February 19th, 2007, 08:26 AM
|
|||
|
|||
|
Random String in mysql
Is there any build in function availabe in MySQL to generate
random strings ?
|
|
#2
February 19th, 2007, 10:08 AM
|
||||
|
||||
|
Code:
UPDATE `table` SET col = MD5(RAND()) WHERE condition=TRUE;
|
|
#3
February 20th, 2007, 01:07 AM
|
|||
|
|||
|
Quote:
Hi, This works.. Thanks... but the result comes with the combination of both numeric and alphabets. I need the random string should contain only alpha both upper and lower case.
|
|
#4
February 20th, 2007, 03:43 AM
|
||||
|
||||
|
Are you sure it has to be native MySQL? PHP can do this just as fast at runtime:
Code:
$q = "UPDATE `table` " .
"SET col = " . substr(str_shuffle("abcefghijklmnopqrstuvwxyz" .
"ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 16) .
"WHERE condition=TRUE";
|
|
#5
September 24th, 2007, 04:05 PM
|
|||
|
|||
|
Quote:
i think this can be done purely in mysql as well.. like Code:
UPDATE `table` SET col = substring(MD5(RAND()), -8) WHERE condition=TRUE; which will generate an 8 character random string for each row/column where the condition holds true..
|
|
#6
September 24th, 2007, 04:09 PM
|
||||
|
||||
|
Please read the entire thread...
|
|
#7
October 2nd, 2007, 04:47 PM
|
|||
|
|||
|
oops you got me there sorry, i was drunk when i posted that... anyways heres a bare one.. you can do this entirely in MySQL without PHP
Code:
UPDATE `table` SET col = (SELECT concat( ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ), ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ) ) ) WHERE condition=TRUE; I know this is long.. but if you want to go an extra and can or would like to implement this within a transaction, then you can shorten the code above dramatically by two ways. 1.) Enclose the above query within a Transaction then Store the alphabets in a variable like Code:
SELECT @alphavar:= a','b','c','d','e','f','g','h','i','j','k','l','m' ,'n','o','p','q','r','s','t','u','v','w','x','y',' z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z'; and call above to all parts of the query calling the characters and/or 2.) Wrap it around a while statement. Hope that helps.. i would just like to emphasize no matter how dirty.. generating random strings within MySQL is possible.. and i know there is a cleaner way than this that is yet to be invented
|
|
#8
October 2nd, 2007, 05:54 PM
|
|||
|
|||
|
or..
you can set up a function to generate a random one character alpha like this: Code:
DROP function if exists generate_alpha; CREATE FUNCTION generate_alpha () RETURNS CHAR(1) RETURN ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m ','n','o','p','q','r','s','t','u','v','w','x','y', 'z', 'A','B','C','D','E','F','G','H','I','J','K','L','M ','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z' ); ) then you can update your table like this in a bit shorter query like Code:
UPDATE `table` SET col = (SELECT concat( generate_alpha (), generate_alpha (), generate_alpha (), generate_alpha (), generate_alpha (), generate_alpha (), generate_alpha (), generate_alpha () ) ) WHERE condition=TRUE; then maybe optimize this further by then again doing this query within a Transaction and enclose the 1st 7 generate_alpha (), within a WHILE statement.. this should get shorter as someone thinks of it.. but for now this should work as it should be within MySQL bounds without help of any other languages.. 
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > Random String in mysql |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
