[mysql] creating a random number using MYSQL

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

You could create a random number using FLOOR(RAND() * n) as randnum (n is an integer), however if you do not need the same random number to be repeated then you will have to somewhat store in a temp table. So you can check it against with where randnum not in (select * from temptable)...

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

these both are working nicely:

select round(<maxNumber>*rand())

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

This is correct formula to find integers from i to j where i <= R <= j

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

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