MySQL Generate Random String, Email Address, URL

vicnumb Feb 27, 2013

Feb 27 2013 Published by under PHP & MySQL

Testing functionality of a php program you will need a populated mysql database.

MySQL database is possible to populate without involving any third party scripting, by mysql own inbuilt functions.

MySQL function to generate a random string of predefined characters with specified length

 

DROP function if exists genstring;
delimiter $$
CREATE FUNCTION genstring(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,ELT(1+FLOOR(RAND() * 26), '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'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$

 

FLOOR(RAND() * 26) – the number 26 here, means the number of characters you use. In case you modify the table of characters, change this number accordingly.

Calling the random string generator mysql function
SELECT genstring(ROUND((RAND() * (maxlength-minlength))+minlength));

Examples:

1. Generate a MySQL random string of 1 to 128 characters

SELECT genstring(ROUND((RAND() * (128-1))+1));

 

2. Generate a MySQL random url-like string

SELECT CONCAT('http://', genstring(ROUND((RAND() * (30-5))+5)), '.',  genstring(ROUND((RAND() * (5-2))+2)));

 

3. Generate a MySQL random email address

SELECT CONCAT(genstring(ROUND((RAND() * (30-5))+5)), '@', genstring(ROUND((RAND() * (30-5))+5)), '.',  genstring(ROUND((RAND() * (5-2))+2)));

 

In case you need some realistic data, like names, dates addresses I’d suggest using the mysql random data generator tool at generatedata.com

No responses yet

Leave a Reply