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

Please visit this link http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select — DevelopmentIsMyPassion, Feb 10 '13 at 14:23

Ja͢ck · Accepted Answer · 2014-12-15T11:06:36.127

161

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

edited Dec 15 '14 at 11:06

answered Feb 10 '13 at 14:20

Ja͢ck

170,779
38
263
309

how I use this code. I tried like this - FLOOR(RAND() * 401) + 100 as number, but not working – TNK Feb 10 '13 at 14:22
NOTE: I dont have a column named 'number' in my db. This must be dynamically generated column. is it posible? – TNK Feb 10 '13 at 14:24
1

@EdHeal Actually I think `round()` will give a non-uniform distribution. – Ja͢ck Feb 10 '13 at 14:32
@Jack it is not working with my real query.. why is it? when selecting only 'random_number' its working. – TNK Feb 10 '13 at 14:58
There is an error message when running this with my real query – TNK Feb 10 '13 at 15:10
I use this with my real code something like this.... $q = "SELECT FLOOR(RAND() * 401) + 100 AS like, tcs.tutor_id AS tid, tcs.category_id AS cid, tcs.subject_id AS sid,.....and so on – TNK Feb 10 '13 at 15:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24269/discussion-between-tharanga-nuwan-and-jack) – TNK Feb 10 '13 at 15:12
1

Better store the result to a variable and use the variable if you have master slave replication. `SET @r=FLOOR(RAND() * 401) + 100`, then `SELECT @r`. – Mar 23 '16 at 06:55
@ElgsQianChen what use-case would that be for? replication is only for updating statements, so not sure how that would be useful :) – Ja͢ck Mar 23 '16 at 06:57
3

`RAND()`, `UUID()`, `NOW()` are indeterministic functions. The calling of such functions should be avoid from being written into the bin log for replication. For example. `INSERT INTO t SET ID=UUID();` will cause the value of the `ID` fields to be different on master and slaves. Instead it needs to be written as `SET @uuid:=UUID();`, and then `INSERT INTO t SET ID=@uuid;`, then run them in a single transaction. This will be replication safe. This is a bit off topic for this question. It doesn't say your answer has any problem. :) – Mar 23 '16 at 07:11
@ElgsQianChen right, didn't think about that, interesting :) – Ja͢ck Mar 23 '16 at 11:22
To always get a value between 100 and 500 you have to do `FLOOR(RAND() * 400.9999...999)) + 100`. If you replace `RAND()` with `1` (a possible value) in your code you'll see it returns `501`. That may or may not be a problem. – Erk Aug 14 '16 at 13:41
@Erk I don't think 1 is a possible value, the range should be [0, 1) – Ja͢ck Aug 19 '16 at 09:37
@Ja͢ck You are right! The documentation actually says: "Returns a random floating-point value v in the range 0 <= v < 1.0." Thanks for that! (http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand) – Erk Aug 21 '16 at 11:26
@ElgsQianChen Is it a problem for `SELECT` statements as well? – glglgl Nov 01 '18 at 11:51
No, except `SELECT INTO`. Essentially you should avoid these indeterministic function calls being written to the bin/relay log. `SELECT` statements won't replicated. – Nov 02 '18 at 07:44

score 11 · Answer 2 · answered Feb 10 '13 at 14:28

11

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

answered Feb 10 '13 at 14:28

Ed Heal

59,252
17
87
127

This coding working like this - SELECT ROUND(100.0 + 400.0 * RAND()) AS random_number, but now working with my query – TNK Feb 10 '13 at 14:32
random_number column must be randomly generated column along with my query. – TNK Feb 10 '13 at 14:39
1

This method will make the first and last number less likely. – Slobodan Pejic May 01 '17 at 16:25

score 5 · Answer 3 · answered Mar 15 '16 at 16:58

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

score 4 · Answer 4 · answered Nov 01 '18 at 11:43

4

these both are working nicely:

select round(<maxNumber>*rand())

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

answered Nov 01 '18 at 11:43

KhalilPan

65
8

score 3 · Answer 5 · answered Feb 10 '13 at 14:24

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)...

score 0 · Answer 6 · edited Mar 27 '14 at 11:54

0

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

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

edited Mar 27 '14 at 11:54

Uri Agassi

36,848
14
76
93

answered Mar 27 '14 at 11:29

ﻂﺎﻫﺭ ﻏﻔﺎﺮ

49
2

3

This is wrong, it will never produce j (or max). It produces a number i <= R < j. – jlh Oct 08 '15 at 20:55
1

Should be: `FLOOR(min+RAND()*(max-min+1))` – David Rodrigues Feb 04 '16 at 17:40

creating a random number using MYSQL

6 Answers6

Linked