creating a random number using MYSQL

Question

I would like to know is there a way to select randomly generated number between 100 and 500 along with a select query.

Eg: SELECT name, address, random_number FROM users

I dont have to store this number in db and only to use it to display purpose.

I tried it something like this, but it can't get to work..

SELECT name, address, FLOOR(RAND() * 500) AS random_number FROM users

Hope someone help me out. Thank you

Answer 1

This should give what you want:

FLOOR(RAND() * 401) + 100

Generically, FLOOR(RAND() * (<max> - <min> + 1)) + <min> generates a number between <min> and <max> inclusive.

Update

This full statement should work:

SELECT name, address, FLOOR(RAND() * 401) + 100 AS `random_number` 
FROM users

Answer 2

As RAND produces a number 0 <= v < 1.0 (see documentation) you need to use ROUND to ensure that you can get the upper bound (500 in this case) and the lower bound (100 in this case)

So to produce the range you need:

SELECT name, address, ROUND(100.0 + 400.0 * RAND()) AS random_number
FROM users

Answer 3

Additional to this answer, create a function like

CREATE FUNCTION myrandom(
    pmin INTEGER,
    pmax INTEGER
)
RETURNS INTEGER(11)
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
BEGIN
  RETURN floor(pmin+RAND()*(pmax-pmin));
END; 

and call like

SELECT myrandom(100,300);

This gives you random number between 100 and 300