MySQL获得两个值之间的随机值 [英] MySQL get a random value between two values

问题描述

我连续有两列:min_value，max_value.有没有办法做像这样的选择:

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

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

我确实意识到RAND做的事情与众不同；我最近一次(在帮助下)出现的是(RAND() * (max-min))+min，尽管它会生成一个浮点数，然后我需要将其转换为ROUND()，这完全是错误的.

I do realize that RAND does 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.

除非有人可以提出替代方案(这将非常有用)，否则我将采用PHP.

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

推荐答案

实际上，ROUND((RAND() * (max-min))+min)是MySQL中执行所需操作的最佳方法.这也是ActionScript，JavaScript和Python中的最佳方法.老实说，我更喜欢PHP方式，因为它更方便.

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.

因为我不知道要返回多少行，所以我不能建议您使用PHP还是MySQL更好，但是如果要处理大量的值，您可能最好使用MySQL.

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中是否更好.我没有参加关于原则的辩论，而是运行了以下内容:

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:

<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的速度提高了大约2-3％.

MySQL is faster by about 2-3%.

但是，如果使用此方法(请注意，MySQL返回更多列):

If you use this, however (note, more columns return by 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落后3-4％(非常不一致的结果)(如果不为$ r [2]使用数组索引分配，则结果大致相同).

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]).

看来，主要区别在于返回PHP的记录数，而不是随机化系统本身.因此，如果您需要A列，B列和一个随机值，请使用PHP.如果只需要随机值，请使用MySQL.

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.

这篇关于MySQL获得两个值之间的随机值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！