0

Is there a similar function like mt_rand for MYSQL? I've searched everywhere but can't seem to find one.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
jessica
  • 1,667
  • 1
  • 17
  • 35
  • RAND() with no seed might work similar to mt_rand(). As of getting a random number in an interval, the only idea is to build the interval using the RAND() output http://stackoverflow.com/questions/14865632/mysql-update-with-random-number-between-1-3 – Alfabravo Aug 26 '15 at 21:26

1 Answers1

1
DELIMITER $$

CREATE FUNCTION `my_rand`(`arg_min` INT, `arg_max` INT) RETURNS int(11)
BEGIN
    RETURN ROUND( arg_min + RAND( ) * (arg_max-arg_min) );
END

invocation:

SELECT my_rand(10,15)

will output one integer number between 10 and 15.

As a sidenote... if you wanted the benefits of mt_rand, intending 'Marsenne Twister', then I fear you should look for an UDF implementation (maybe you should check this Statistics for mySQL).

If instead you look for a function that simply spits out a random integer between min and max, this should do the trick.

also, if we used FLOOR as a rounding function, it would have been impossible to get 15 as a result of my example.

this is because, from the docs:

RAND() Returns a random floating-point value v in the range 0 <= v < 1.0.

Reading immediately after RAND, you can find ROUND in the docs... it states that ROUND has pretty implementation-dependant behaviour for rounding, so its behaviour may change (and has changed) between mysql versions. This is probably the reason why the docs suggest rounding RAND with FLOOR instead of ROUND. So, basically it seems debatable which choice is best... "FLOOR or ROUND, make your choice..."

Finally... take a look at this article. It may seem scary but at least it makes it clear that the question was 'difficult', indeed.

Order by RAND() by Jan Kneschke

Leo128
  • 163
  • 12
  • I'm actually trying to just get a random row from the database, but i want the row to be really, really random, as oppose to the not too random, random. – jessica Aug 26 '15 at 21:51
  • this should give more indication then: http://stackoverflow.com/questions/7966430/how-to-sample-rows-in-mysql-using-randseed – Leo128 Aug 26 '15 at 21:59
  • That's good for a fixed database. A database that doesn't get new rows inserted into it often, like the OP said. Unfortunately mine does. – jessica Aug 26 '15 at 22:19
  • I wouldn't be that sure. you can use rand() in your order by... this is perfectly okay even if the contents of the db changes. the problem with the OP in the linked post is that he doesn't really need only a simple random selection. he stated: 'My problem is that the query may be run repeatedly and if a row is selected once it must always be selected. So using a constant seed isn't going to work'. – Leo128 Aug 26 '15 at 22:43
  • rand() isn't as random as mt_rand though, and mysql doesn't have a mt_rand function. – jessica Aug 27 '15 at 01:34
  • It seems someone made it thru UDF. take a look at the link I provided in my answer... https://www.xypron.de/projects/sqlstat/rand_mt.html it is not commercial as I thought. I have never covered this solution, please let me know if it solves your problem – Leo128 Aug 27 '15 at 01:39