mysql set seed for rand

Question

Is it possible to set the seed for the RAND() function in MySQL? I need this for unit testing, to make sure I know what the expected outcome will be.

In PHP one would simply do:

<?php srand(12345); echo rand(); ?>

In my model I currently have a query like:

SELECT * FROM table ORDER BY RAND() LIMIT 1;

Now in my unit test I want to make sure I know what the seed for RAND() is so I know which record the query returns.

Maybe by performing an extra query before the query in my model?

I know I can just supply add an argument to RAND(), but this is not what I want; I do not want to modify the query.

PS. I am using Linux; will it help to set the seed for /dev/random maybe?

score 3 · Answer 1 · answered Nov 15 '12 at 10:52

3

You can refer to this: MySQL RAND with with parameter

The RAND function support a parameter RAND(N) which will be used as a seed.

answered Nov 15 '12 at 10:52

Plamen Nikolov

2,643
1
13
24

I know, but then I have to modify the query which is not what I want. – meijuh Nov 15 '12 at 11:01

score 0 · Answer 2 · answered Nov 15 '12 at 14:10

0

Generally, we don't unit test a SQL function, we unit test our code that calls the SQL function.

So perhaps rather than testing for a specific return value, just test that you get some value. If the query is returning a value, you know it is "working" and you remain insulated from the inner technicals of MySQL. You also don't have to modify your query.

answered Nov 15 '12 at 14:10

OrangeWombat

315
4
11

Yes I think I have to go with this. So there is no way to set the seed? – meijuh Nov 19 '12 at 10:55
Only as a parameter, as suggested by @Plamen Nikolov. – OrangeWombat Nov 20 '12 at 13:27
Unfortunately, setting the seed in one query does not affect subsequent calls to RAND(). – OrangeWombat Nov 20 '12 at 13:38

score 0 · Answer 3 · answered Apr 01 '22 at 10:43

To add to this discussion, I think it is useful to do limited unit testing on database queries, procedures and functions too as this is part of the business logic.

One way how to get have a deterministic rand is to use now() as the seed in rand. And now() can be set by the test case in the database session like this:

SET timestamp=UNIX_TIMESTAMP('2022-01-01 11:38:01')

subsequent calls to RAND(NOW()) will now always have the same result.

Note that the trick above is very useful for any queries as well in which you want to mock a time

mysql set seed for rand

3 Answers3