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

User · Accepted Answer

This should give what you want   FLOOR RAND     401    100   Generically  FLOOR RAND       lt max gt  -  lt min gt    1      lt min gt  generates a number between  lt min  and  lt max gt  inclusive   Update  This full statement should work   SELECT name  address  FLOOR RAND     401    100 AS  random number   FROM users

User · Answer

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

User · Answer

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

User · Answer

As RAND produces a number  0  lt   v  lt  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

User · Answer

This is correct formula to find integers from i to j where i  lt   R  lt   j  FLOOR min RAND    max-min

User · Answer

these both are working nicely   select round  lt maxNumber gt  rand        FLOOR RAND       lt max gt  -  lt min gt    1      lt min gt     generates a number between  lt min gt  and  lt max gt  inclusive

[mysql] creating a random number using MYSQL

Examples related to mysql

Examples related to sql