Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL get a random value between two values

Tags:

php

mysql

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.

like image 643
Gajus Avatar asked Jul 01 '11 15:07

Gajus


People also ask

What is rand () in MySQL?

RAND() Return a random floating-point value.

How do I generate a random number in SQL?

Random Integer RangeSELECT FLOOR(RAND()*(b-a+1))+a; Where a is the smallest number and b is the largest number that you want to generate a random number for. SELECT FLOOR(RAND()*(25-10+1))+10; The formula above would generate a random integer number between 10 and 25, inclusive.

How do you generate random unique numbers in MySQL?

SQL Server has a built-in function that generates a random number, the RAND() mathematical function. The RAND math function returns a random float value from 0 through 1. It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.


2 Answers

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.

like image 98
cwallenpoole Avatar answered Oct 15 '22 10:10

cwallenpoole


This method guarantees the same statistical probability for each value:

SELECT FLOOR((RAND() * (max-min+1))+min) 
like image 36
Karolis Avatar answered Oct 15 '22 11:10

Karolis