Generate a random string in MySQL

Question

I'm trying to get a random string in phpmyadmin using a function. I have the following code:

CREATE FUNCTION randomPassword()
RETURNS varchar(128)
BEGIN

    SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    SET @charLen = length(@chars);

    SET @randomPassword = '';

    WHILE length(@randomPassword) < 12
        SET @randomPassword = concat(@randomPassword, substring(@chars,CEILING(RAND() * @charLen),1));
    END WHILE;

    RETURN @randomPassword ;
END;

Now I get the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Does anyone know how I can fix this?

Answer 1

This is faster than concat + substring routine.

select substring(MD5(RAND()),1,20);

As I've tested inserting 1M random data, md5 routine consumes only 1/4 (even less) time of concat + substring routine;

The problem is a md5 string contains only 32 chars so if you need a longer one you'd have to manually generate more md5 strings and substring it yourself.

Answer 2

Try this more simple solution:

SELECT CONV(FLOOR(RAND() * 99999999999999), 10, 36)

Answer 3

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE( TO_BASE64(MD5(RAND())), '=',''),'+',''),'/',''), 2, 40)

This solution to generate a fixed length random string that contains all lower- and upper-case chars and digits.

SELECT SUBSTRING(REPLACE(REPLACE(REPLACE( TO_BASE64(MD5(RAND())), '=',''),'+',''),'/',''), 2, FLOOR(10+RAND()*31))

If you need a random length string (from 10 to 40 symbols in this example)