mysql 随机组合_MySQL查询从两个表中接收随机组合

Alright, here is my issue, I have two tables, one named firstnames and the other named lastnames. What I am trying to do here is to find 100 of the possible combinations from these names for test data. The firstnames table has 5494 entries in a single column, and the lastnames table has 88799 entries in a single column. The only query that I have been able to come up with that has some results is:

select * from (select * from firstnames order by rand()) f LEFT JOIN (select * from lastnames order by rand()) l on 1=1 limit 10;

The problem with this code is that it selects 1 firstname and gives every lastname that could go with it. While this is plausible, I will have to set the limit to 500000000 in order to get all the combinations possible without having only 20 first names(and I'd rather not kill my server). However, I only need 100 random generations of entries for test data, and I will not be able to get that with this code. Can anyone please give me any advice?

解决方案

Is this what you're looking for?

SELECT *

FROM (

SELECT firstname

FROM firstnames

ORDER BY RAND( )

LIMIT 10

) AS tb1

JOIN (

SELECT lastname

FROM lastnames

ORDER BY RAND( )

LIMIT 10

) AS tb2 ON 1=1

This will give you all combinations of 10 random first names and 10 random last names. Change the limits to change how many names you want to combine.