php MySQL 获取两个值之间的随机值

MySQL get a random value between two values

提问by Gajus

I have two columns in a row: min_value, max_value. Is there a way to do a select like:

我连续有两列：min_value, max_value。有没有办法做一个选择：

SELECT RAND(`min_v`, `max_v`) `foo` [..]

I do realize that RANDdoes a different thing; the closest I came up (with help) is (RAND() * (max-min))+min, though it will produce a float number, which I'd need then to ROUND() and this is just completely wrong.

我确实意识到这RAND有不同的作用；我想出的最接近的（在帮助下）是(RAND() * (max-min))+min，尽管它会产生一个浮点数，然后我需要它来 ROUND() ，这完全是错误的。

Unless anyone can suggest an alternative (which would be very useful), I will go PHP way.

除非有人可以提出替代方案（这将非常有用），否则我将采用 PHP 方式。

回答by cwallenpoole

Actually, ROUND((RAND() * (max-min))+min)is the best way in MySQL to do what you'd like. It is also the best way in ActionScript, JavaScript, and Python. Honestly, I prefer it to the PHP way because it is more convenient.

实际上，这ROUND((RAND() * (max-min))+min)是在 MySQL 中做你想做的最好的方式。它也是 ActionScript、JavaScript 和 Python 中的最佳方式。老实说，我更喜欢它而不是 PHP 方式，因为它更方便。

Because I don't know how many rows you'll be returning, I can't advise you whether it is better to use PHP or MySQL for this, but if you're dealing with a large number of values you probably are better off using MySQL.

因为我不知道您将返回多少行，所以我无法建议您为此使用 PHP 还是 MySQL 更好，但是如果您正在处理大量值，您可能会更好使用 MySQL。

Addendum

附录

---

---

So, there was a question as to whether this is better in PHP or MySQL. Instead of getting into a debate on principles, I ran the following:

所以，有一个问题是这在 PHP 还是 MySQL 中更好。我没有进入关于原则的辩论，而是进行了以下操作：

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL is faster by about 2-3%.

MySQL 快了大约 2-3%。

If you use this, however (note, more columns return by MySQL):

但是，如果您使用它（注意，MySQL 会返回更多列）：

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) as rd, 200 as two, 10 as ten FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL comes out behind by 3-4% (very inconsistent results) (about the same results if you don't use an array index assignment for $r[2]).

MySQL 落后 3-4%（结果非常不一致）（如果您不为 $r[2] 使用数组索引分配，结果大致相同）。

The major difference, it seems, comes from the number of records return to PHP and not the randomization system itself. So, if you need column A, column B, and a random value, use PHP. If you only need the random value, then use MySQL.

主要区别似乎来自返回到 PHP 的记录数量，而不是随机化系统本身。因此，如果您需要 A 列、B 列和一个随机值，请使用 PHP。如果您只需要随机值，则使用 MySQL。

回答by Karolis

This method guarantees the same statistical probability for each value:

此方法保证每个值具有相同的统计概率：

SELECT FLOOR((RAND() * (max-min+1))+min)

回答by Kyle

Could you do something like this?

你能做这样的事情吗？

SELECT id, (FLOOR( 1 + RAND( ) *60 )) AS timer
FROM users
LIMIT 0 , 30

See this post

看到这个帖子

回答by AamirR

in case minimum range is 1, you can simply

如果最小范围为 1，您可以简单地

SELECT FLOOR((RAND() * max_range) + 1)

in case minimum range is 0, you can even more simply

如果最小范围为 0，您可以更简单地

SELECT FLOOR((RAND() * max_range))

回答by Luke Watts

Depending on how many rows you have in the table(s), using rand() in a query or subquery can be extremely slow.

根据表中有多少行，在查询或子查询中使用 rand() 可能会非常慢。

You can seriously improve the speed by first putting the random value in a variable and then just using that in your query.

您可以通过首先将随机值放入变量中然后在查询中使用它来真正提高速度。

For example on a table with over 4 million rows...

例如在一个超过 400 万行的表上......

This took over 10 minutes:

这花了 10 多分钟：

SELECT
    *
FROM
    `customers` `Customer`
WHERE
    `id` = (
        SELECT
            FLOOR((RAND() * (max(`CustomerRand`.`id`) - min(`CustomerRand`.`id`) + 1)) + min(`CustomerRand`.`id`)) `random_id`
        FROM
            `customers` `CustomerRand`
    );

While this took about 3 seconds on average:

虽然这平均需要大约 3 秒：

SELECT
   FLOOR((RAND() * (max(`CustomerRand`.`id`) - min(`CustomerRand`.`id`) + 1)) + min(`CustomerRand`.`id`)) `random_id`
FROM `customers` `CustomerRand` INTO @rand_id;

SELECT * FROM `customers` WHERE `id` = @rand_id;

You might even be able to put this into a stored procedure then if it's something you would want to do or re-use often. The stored procedure could then be called from PHP or Python or wherever

您甚至可以将其放入存储过程中，然后如果您想要这样做或经常重复使用它。然后可以从 PHP 或 Python 或任何地方调用存储过程