MySQL get a random value between two values

Question

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` [..]

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.

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

Please describe the task. Maybe you don't need rand() at all. — scaryzet, Jul 01 '11 at 15:49
I need to get a random value between two values in columns `min_v` and `max_v` respectively. — Gajus, Jul 01 '11 at 15:57

cwallenpoole · Accepted Answer · 2013-05-29T05:36:39.020

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.

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.

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:

<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%.

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

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.

Ok. I am surprised, because that is vice-versa of the kind of answer I expected to hear. Honestly, I think that MySQL will handle this **much** slower than PHP. Anyone mind commenting that? as a general practice, I always avoid any math in MySQL query. — Gajus, Jul 01 '11 at 15:56
Try both ways and measure it ;) There's never too early for premature optimization! — André Laszlo, Jul 01 '11 at 15:59
I could be wrong. I'll run some benchmarks and then we'll know. — cwallenpoole, Jul 01 '11 at 16:02
This method has some issues: the first and the last values has twice less probabilities that other numbers. But everything depends on particular application needs. — Karolis, Jul 01 '11 at 16:38
@cwallenpoole, You are using static numbers, rather than column values. I bet using the actual column values would make the MySQL method much slower. — Gajus, Jul 01 '11 at 22:54
@Guy That is a very good point. Do you have a data set ready? Because I think that this type of stuff is great to know. — cwallenpoole, Jul 01 '11 at 22:57
When one says "random number between a and b" they usually mean "all integers between those two numbers inclusively with the same probability". This answer gets that wrong, see @Karolis's answer for the correct solution. — AndreKR, Jan 05 '15 at 05:11

Karolis · Answer 2 · 2011-07-01T16:48:58.587

47

This method guarantees the same statistical probability for each value:

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

edited Jul 01 '11 at 16:48

answered Jul 01 '11 at 15:53

Karolis

9,396
29
38

I need a random value between those two variables, not one or the other. – Gajus Jul 01 '11 at 15:58
@Guy sorry. I was inattentive :) Updated! – Karolis Jul 01 '11 at 16:50
1

This is the same option as offered by @Kyle. There is no point repeating the same answer. – Gajus Jul 01 '11 at 22:51
@Guy If somebody is trying to help you it doesn't mean he is trying to repeat somebody's answer :) Not sure I saw @Kyle's answer when I was writing this post (because of undeleting??). In any case +1 to @Kyle for taking into account the math. – Karolis Jul 02 '11 at 07:15
the same answer is even mentioned in my question, though using ROUND rather then FLOOR. Neither is good though. Function should randomly do either CEIL or FLOOR to work perfectly. – Gajus Jul 02 '11 at 08:55
@Guy All of us gave you almost the same answer :) I just wanted to note that FLOOR method is much better from mathematical point of view. In any case your question has a an obvious answer, so you would not expect something different and unusual. Even if there were such a function like `RAND(min_v, max_v)`, this would not be much faster because the need of internal calculations would be very similar. – Karolis Jul 02 '11 at 09:26
this was exactly what i need it :) – Nisanio Feb 06 '13 at 13:15

score 3 · Answer 3 · answered Apr 14 '16 at 07:33

3

in case minimum range is 1, you can simply

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

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

SELECT FLOOR((RAND() * max_range))

answered Apr 14 '16 at 07:33

AamirR

11,672
4
59
73

score 2 · Answer 4 · edited May 23 '17 at 11:47

2

Could you do something like this?

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

See this post

edited May 23 '17 at 11:47

Community

1
1

answered Jul 01 '11 at 15:48

Kyle

4,202
1
33
41

score 0 · Answer 5 · answered Aug 14 '19 at 15:19

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

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

This took over 10 minutes:

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:

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

score 0 · Answer 6 · answered Feb 24 '21 at 17:54

0

You can use order by rand

SELECT virtual.num
FROM (
         SELECT 1 AS num UNION
         SELECT 2 AS num UNION
         SELECT 3 AS num
     ) virtual
ORDER BY RAND()
LIMIT 1

answered Feb 24 '21 at 17:54

anydasa

2,616
1
15
13

MySQL get a random value between two values

6 Answers6

Addendum

Linked