Generate Random Names For MySQL Database

Question

I need to generate in the region of 2,000 random names to be inserted into a MySQL table in three fields: FirstName, LastName, Gender (as per ISO 5218). To help in this I have lists of 50 male first names, 50 female first names and 100 surnames. These could become temporary database tables if necessary. I feel that doing it in MySQL is maybe a sensible step. I do not know how to code the SQL to achieve this. Is this possible?

Answer 1

You could create a FIRST_NAMES table, insert all first names (male and female), then create a LAST_NAMES table, and then create the RANDOM_NAMES table doing something like:

INSERT INTO RANDOM_NAMES
VALUES (SELECT first FROM FIRST_NAMES
        ORDER BY RAND()
        LIMIT 1,
        SELECT last FROM LAST_NAMES
        ORDER BY RAND()
        LIMIT 1)

ORDER BY RAND() returns the table rows in random order, and LIMIT 1 only returns the first row, which is what you want.

Notice the above code inserts a single row, so if you want to insert 2000 rows, you need to iterate. For the iteration part, the first answer for this topic seems like a good bet.

This seems like a very inefficient way of doing this, however. The better way to do it would be to use the INSERT-SELECT construct. What you will do here is create a table with the cartesian product of the FIRST_NAMES and LAST_NAMES tables, order the rows randomly, and limit the query to 2000 rows, then insert those rows into your new table.

Something like:

INSERT INTO RANDOM_NAMES (first_name, last_name)
  SELECT first, last
  FROM FIRST_NAMES, LAST_NAMES
  ORDER BY RAND()
  LIMIT 2000

I am no SQL coder, so you might want to check if this works. But it seems like the most efficient way of tackling your problem, as you are avoiding iterating.