How do I create a random string in MySQL?

There are lots of quick and dirty ways to create a random strings in mysql.
If you want letters and numbers, just do this:

SELECT LOWER(
SUBSTRING(
md5( RAND( 4 ) ) ,
FLOOR ( 7 + ( RAND( ) * 14 ) ) ,
FLOOR( 3 + ( RAND () * 4 ) ) ) ) AS fname

However this method will allow you to create the a random string with a specifc set of characters. For example you can specify that you want only alpha characters and no numbers. Or you could generate hexadecimal numbers by specifying just that character set:

DROP function if exists generate_word;
DELIMITER $$
CREATE FUNCTION generate_word (counter smallint) RETURNS varchar(255)
BEGIN
DECLARE result CHAR(255) ;
DECLARE oldword CHAR(255) ;
DECLARE newchar CHAR(1) ;
set result = “”;
repeat
set result = 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’ ),
result);
set counter = counter – 1;
until counter = 0
end repeat;
RETURN result;
END$$

DELIMITER ;
select generate_word(FLOOR( 3 + ( RAND( ) * 14 ) )),generate_word(FLOOR( 3 + ( RAND( ) * 14 ) )),generate_word(FLOOR( 3 + ( RAND( ) * 14 ) )),generate_word(FLOOR( 3 + ( RAND( ) * 14 ) )),generate_word(FLOOR( 3 + ( RAND( ) * 14 ) ));

Hope that helps someone

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.