How to Generate Random Values Within a Range in MySQL?

Obtaining a Random Value Within a Range in MySQL

Introduction

When working with MySQL, there may be instances where you need to generate a random value that falls within a specified range. While the RAND() function exists, it does not fulfill this requirement. This article delves into the best approach for achieving this in MySQL.

Solution:

The optimal method in MySQL to generate a random value within a specified range is:

ROUND((RAND() * (max-min))+min)

• RAND(): Generates a random number between 0 and 1.
• max: Maximum value of the desired range.
• min: Minimum value of the desired range.

This formula multiplies RAND() by the difference between max and min, adding min to the result. ROUND() is applied to obtain an integer value.

Comparison with PHP Implementation:

The PHP equivalent of the MySQL solution is:

<code class="php">rand($min, $max)</code>

However, benchmarks have shown that the MySQL solution is slightly faster when dealing with a large number of values. The choice between PHP and MySQL depends on the number of rows being processed and whether only the random value is needed or additional columns will be returned.

Example Query

To obtain a random value between 10 and 200:

<code class="mysql">SELECT ROUND((RAND() * (200-10))+10) AS `foo`</code>

Addendum: Performance Comparison

To further illustrate the performance differences, the following PHP script was run:

<code class="php">// MySQL
$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);

// PHP
$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);</code>

The results show that MySQL is faster for generating random values if only the random value is needed, but slower if additional columns are returned.

The above is the detailed content of How to Generate Random Values Within a Range in MySQL?. For more information, please follow other related articles on the PHP Chinese website!