Generating random names in MySQL

I’ve improved my earlier random string generation procedures to better suit my needs. So I created a Random Name Generator for MySQL.

I’ve created two new procedures. They pick from the 100 most popular first names (well actually the 50 most popular male and 50 most popular female first names for the US) and the 100 most popular surnames (for the US).

Using these two procedures generate_fname() and generate_lname() you can create realistic random names and email addresses for your tests.

You can download the SQL here.

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