2

What is precision of MYSQL RAND() function?

I can't find it on the official page: MYSQL RAND() function is told to return floating-point number, unfortunately it's precision is not stated in a clear way. It can be a single-precision floating-point data, or double-precision, or any other kind of data.

What I would like to know exactly is - what is the maximum integer range [0,N] in which I can generate random integer numbers with FLOOR(RAND()*N) such that there won't be any "skips" and any number from 0 to N can be generated?

Another thing which I would like to know: How to generate numbers, which are bigger than N in MySQL?

klm123
  • 12,105
  • 14
  • 57
  • 95
  • Will this answer your question? https://en.wikipedia.org/wiki/IEEE_754 – Thallius Sep 23 '21 at 15:06
  • Does this answer your question? [How to get mysql random integer range?](https://stackoverflow.com/questions/984396/how-to-get-mysql-random-integer-range) – kmoser Sep 23 '21 at 16:35
  • @kmoser, no? why do you think it can answer my question? my question about precision of rand() output and that question seems to be a question about general use of rand function (And my question actually includes answer to that question - FLOOR(RAND()*N)) – klm123 Sep 23 '21 at 17:49
  • @ClausBönnhoff, this is explanation on what floating point number is. And it is stated on the very same wiki page you told that it can have different precisions. My question is what the precision of the RAND() function output. – klm123 Sep 23 '21 at 17:50
  • Mysql uses IEEE_754 floating point format. My article shows the precision of this format. So what else do you want to know? – Thallius Sep 23 '21 at 18:02
  • @ClausBönnhoff, IEEE_754 floating point format can have any kind of precision. And this is exactly my question. Which precision RAND() function is using. Plus, there is two other questions I asked above. I've added more explanations in case they were unclear, but so far it is very unclear to me how exactly your link answers my questions. I would appreciate if you make a complete answer from it. – klm123 Sep 23 '21 at 18:22
  • 1
    You can always write a stored procedure that generates a random integer in just about any range, which is guaranteed to not have any "skips", by simply concatenating random digits repeatedly, and casting the result to an integer. – kmoser Sep 24 '21 at 06:13

1 Answers1

1

As written in the MySQL docs the precision is system dependent. So there is not the one answer to your question.

https://dev.mysql.com/doc/internals/en/floating-point-types.html

Since MySQL uses the machine-dependent binary representation of float and double to store values in the database, we have to care about these. Today, most systems use the IEEE standard 754 for binary floating-point arithmetic. It describes a representation for single precision numbers as 1 bit for sign, 8 bits for biased exponent and 23 bits for fraction and for double precision numbers as 1-bit sign, 11-bit biased exponent and 52-bit fraction. However, we can not rely on the fact that every system uses this representation. Luckily, the ISO C standard requires the standard C library to have a header float.h that describes some details of the floating point representation on a machine. The comment above describes the value DBL_DIG. There is an equivalent value FLT_DIG for the C data type float.

At the end I have no clue why the precision of a random number is important in any case. I cannot see any use case

Thallius
  • 2,482
  • 2
  • 18
  • 36
  • Thank you. This helps. If you use 23 bits for fraction then Rand()*2^24 will have only 2^23 outcomes or so. I.e. you can't generate all numbers from 0 to 16777215 with that function, some numbers in that range will never be possible to obtain, not to mention uniform distribution, where every outcome from 0 to 16777215 should have 1/16777216 probability. If you want numbers in range 0 to 10^100 - this is even worse. That's why precision is important. – klm123 Sep 23 '21 at 18:43
  • You can create your random number from calling rand() two times or more and add or multiply them. – Thallius Sep 23 '21 at 19:02