mysql - MySQL 查询从 3 个表中接收随机组合有点麻烦

Question

这是原来的问题:

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?

• 上面的问题已经回答了，我需要关于下面问题的建议。我只是提供它作为背景。

我想将另一个表加入到名为 status 的混合中。该表在一列中有 5 个条目，每当我尝试将它与其他两个连接时，它最终会再次重复另外两个表，以便状态标签适合它们中的每一个。我唯一获得轻微成功的是这个:

SELECT *
FROM ( SELECT firstnames FROM firstnames ORDER BY RAND( )  LIMIT 5 ) AS First
JOIN ( SELECT lastnames FROM lastnames ORDER BY RAND( )  LIMIT 5 ) as Last 
JOIN ( SELECT status FROM status ORDER BY RAND( ) LIMIT 1) AS Status ON 1=1;

虽然名字和姓氏在此查询中不重复，但每个名字和姓氏只列出一个状态标签。非常感谢你们的帮助!

Answer 1

使用 MySQL 变量，您应该能够通过类似的东西来完成……虽然没有明确测试，但这应该可以让您得到想要的东西。您不能进行简单的笛卡尔交叉连接，因为正如您所知，它将获得第一个名字并与所有姓氏连接，然后是下一个名字与所有姓氏...

此查询使用 MySQL 变量。内部查询(预先分别的名字和姓氏)将预先查询您随机的 10 个名字(或姓氏)。然后，将它加入到一个@variable 中，用于名字序列 (@fns) 和姓氏序列 (@lns)。由于两者都只有 10 条记录，并且每条记录都从 0 开始，因此它们都会产生序列为 1-10 的记录，因此 JOIN 将在 SEQUENCE 上，其中每个保证值只会出现一个... vs rand() 返回一些浮点分数，你永远不能保证一个表中的数字与另一个表中的数字匹配。

select
      First10.FirstName,
      Last10.LastName,
      ( SELECT status FROM status ORDER BY RAND( ) LIMIT 1) AS Status
   from 
      ( select fn.FirstName,
               @fns := @fns + 1 as Sequence
           from
             ( select FirsName,
                  from FirstNames
                  order by rand() 
                  limit 10 ) fn,
             (select @fns := 0 ) vars
      ) First10

      JOIN

      ( select ln.LastName,
               @lns := @lns + 1 as Sequence
           from
              ( select LastName,
                   from LastNames
                   order by rand() 
                   limit 10 ) ln,
              (select @lns := 0 ) vars 
      ) Last10

      ON First10.Sequence = Last10.Sequence