MySQL get a random value between two values

user_368691 0
Viewed 73985

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.

mysql php
6 Answers

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

user_54534640

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
user_29875110
Related