Problem with RAND in SQL

Question

I am using Mysql "5.6.38-log - Mysql Community Server (GPL)"

I have a trial that runs every second, and in it has a validation with a RAND:

WHERE t.auc_due_time < GREATEST(LEAST(TRUNCATE(12 * RAND(),0),12),2)

The problem I am facing is that it is always running the query in 5 OR 7 in this Rand, I need it to be always alternating, for example 11,9,3,5,2,7,10,11 etc..

Why isn’t it working that way? Is there some other way to do this?

Answer 1

According to the Mysql documentation, the function RAND([N]), return a floating point between 0 and 1.0. To get a random integer R being i <= R < j, you need the following expression

FLOOR(i + RAND() * (j − i))

For example, for obiter an integer randomic between 7 and 12, use the following code (7 <= R < 12)

SELECT FLOOR(7 + (RAND() * 5));

An integer between 0 and 100 would be next:

SELECT FLOOR(0 + (RAND() * 100));

or

SELECT FLOOR((RAND() * 100));

For a random 2 to 12, you have to do the following:

SELECT FLOOR(2 + (RAND() * 11));

See the example execution on SQL Fiddle