如何确保随机选择的两行彼此不同? [英] How do you make sure two rows selected at random are different from one another?

问题描述

我问了这个问题 昨天 并收到了一个简单的解决方案，但它是关于 ORDER BY rand()，有人告诉我这对于大表来说效率低下.我在网上搜索，发现这是一种更有效的方法:

I asked this question yesterday and received an easy solution, but it was about ORDER BY rand(), which I was told was inefficient for large tables. I searched the web and found this a much more efficient method:

1. 获取表中的总行数:$rows
2. 使用 mt_rand 将 $row1_id 设置为 1 和总行数之间的随机数:$row1_id = mt_rand(1,$rows)
3. 再次使用 mt_rand 将 $row2_id 设置为 1 和总行数之间的随机数:$row2_id = mt_rand(1,$rows)
4. 运行查询以选择随机行，即

1. Get the total number of rows in the table: $rows
2. Use mt_rand to set $row1_id as a random number between 1 and the total number of rows: $row1_id = mt_rand(1,$rows)
3. Use mt_rand again to set $row2_id as a random number between 1 and the total number of rows: $row2_id = mt_rand(1,$rows)
4. Run queries to select random rows, i.e.

mysqli_query($conn,"SELECT * FROM photos WHERE photo_id=$row1_id")mysqli_query($conn,"SELECT * FROM photos WHERE photo_id=$row2_id")

但是，我需要确保 $row1_id != $row2_id(随机生成的数字必须彼此不同).我尝试使用 if 语句，但它只会减少数字相同的可能性，但仍然有可能.

However, I need to make sure that $row1_id != $row2_id (the randomly generated numbers must be different from each other). I tried using an if statement but it only lessened the chances of the numbers being the same, but it was still possible.

有什么简单的解决方案吗?

Any easy solution to this one?

推荐答案

只需在循环中生成第二个随机数，以确保它不等于第一个随机数.这个循环很可能只会执行一次.

Just generate your second random number inside a loop to make sure it isn't equal to the first one. In all likelihood this loop will only ever execute once.

$num1 = mt_rand(...);
$num2 = 0;

do {
  $num2 = mt_rand(...);
} while($num2 == $num1);

// $num1 and $num2 are guaranteed to be different

这个方法对你有用，只要你的行 ID 是连续的，没有间隙

This method will work for you, so long as your row id's are contiguous, with no gaps

如果存在差距，您需要生成新数字，直到两者都导致数据库命中.像这样的东西.

If there are gaps, you'll need to generate new numbers up until both result in database hits. Something like this.

$photo1 = null;
$photo2 = null;

do {
  $num = mt_rand(...);
  $photo1 = mysql_query(...);
} while(mysqli_num_rows($photo1) == 0);

$photo1 = mysqli_fetch_assoc($photo1);

do {
  $num = 0;
  do {
    $num = mt_rand(...);
  } while($num == $photo1['id']);
  $photo2 = mysql_query(...);
} while(mysqli_num_rows($photo2) == 0);

$photo2 = mysqli_fetch_assoc($photo2);]

由您将这些方法与 order by rand() 选项进行比较，看看哪个更高效.

Up to you to compare these methods against the order by rand() options and see which is more performant.

这篇关于如何确保随机选择的两行彼此不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！