Re: sql rand function

On Fri, Nov 11, 2022 at 10:29 PM Jay Vaughn <jeffersonvaughn@xxxxxxxxx> wrote:

Whoah. Is all that necessary?

I like the example in the link Mark sent.

SELECT RAND(MICROSECOND(CURRENT_TIMESTAMP))
FROM MYFILE

And then simply rand() after it’s been seeded.

As I explained in my previous post, you don't even need that much.
Just always use RAND() and don't bother with EVER providing the
parameter.

But, as an academic exercise, supposing the seed worked as described,
you could reduce the chance of a repeated sequence by 1000-fold,
because the parameter is a 4-byte integer.

One way to make use of that (as of 7.2) is to specify more digits of
subsecond precision. Instead of the default 6 (i.e. microseconds), 7.2
lets you get up to 12 (i.e. picoseconds). That's too many for RAND(),
but you can safely pass up to 9 digits. So, for example, you could do

VALUES RAND(INT(MOD(SECOND(NOW(9),9),1)*1000000000)

to seed the random-number generator.

John Y.