0

I have a Double value in a Java application coming from a server. I want to store that value in MySQL, but I don't want any kind of precision loss to happen.

It is sort of storing it as text. How can I define the corresponding data type in a MySQL table for DOUBLE to store it exactly as the application receives it?

I see in the MySQL documentation that the data type requires to pass precision. So I am not sure how to safely define it.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jim
  • 3,845
  • 3
  • 22
  • 47
  • Use `BigDecimal` which is an exact type, and which your JDBC driver can map to an exact numeric column in the SQL table. – Tim Biegeleisen Nov 04 '22 at 10:56
  • @TimBiegeleisen: But what should be the data type for the MySQL table? – Jim Nov 04 '22 at 10:58
  • 1
    Use an exact numeric type, so `NUMERIC` or `DECIMAL`. – Tim Biegeleisen Nov 04 '22 at 11:10
  • You don't really need to worry about precision loss in MySQL when you keep working with double (or Double) in Java. Like the duplicate suggests switch to BigDecimal. And regarding MySQL, that's actually not that hard to research. You can find questions like [How to use MySQL DECIMAL?](//stackoverflow.com/q/4834390) – Tom Nov 04 '22 at 11:13
  • 1
    @Tom: I can't change the data type in the java level. Should I create a `BigDecimal` from the `Double` then and store that? – Jim Nov 04 '22 at 11:16
  • @Tom: Also doesn't `BigDecimal` require number of digits after the decimal? If so how is that different with `Double`? This is what I am not sure about – Jim Nov 04 '22 at 11:18
  • 1
    @Tom that question _also_ doesn't answer the question. And why would you switch to `BigDecimal` if your value is adequately represented by a `Double`? – Ryan M Nov 04 '22 at 11:18
  • 1
    You can do that, but that wouldn't help much. Double is an imprecise data type, so when have a value coming as Double, then you already have precision loss. You can prevent additional loss when creating a BigDecimal from it, but you won't get the 100% correct value which was present on the server. – Tom Nov 04 '22 at 11:19
  • @RyanM How do you know it is adequately represented? I don't know the range of values the server can send and I wouldn't trust that double can represent them all without any loss, then precision loss is a topic OP worries about. – Tom Nov 04 '22 at 11:21
  • @Jim BigDecimal uses integer array internally to store the value, not an approximate value, like double. – Tom Nov 04 '22 at 11:22
  • 1
    @Tom You're assuming there is precision loss, but the question seems to imply that the value is accurately represented in the Java application. Assuming the server is also using 64-bit floating points, there would be no loss of precision. – Ryan M Nov 04 '22 at 11:22
  • @Tom: It not important for me if the server has lost precision when the value was sent. All I need is to store/retrieve the value from MySQL "verbatim" as was received. I understand that `Double` inherently is not precise but it is not relevant for me here – Jim Nov 04 '22 at 11:24
  • Since you already know about `DOUBLE` – why not use that? It's not really clear to me where you see a problem that an answer should address. Note that as [per the MySQL](https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html), the floating point data types do *not* require passing a precision. – MisterMiyagi Nov 04 '22 at 11:48
  • @MisterMiyagi: That says `DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point`. So how would I define those? Check the formats coming from the server? – Jim Nov 04 '22 at 11:55
  • 1
    @Jim As the linked docs says, setting the precision is *optional*. The standard `DOUBLE` syntax is already a regular [double precision number](https://en.wikipedia.org/wiki/Double-precision_floating-point_format) like Java's `Double`. – MisterMiyagi Nov 04 '22 at 12:07
  • @MisterMiyagi: So if I don't define any precision will it store the value "verbatim" or there could be something lost? – Jim Nov 04 '22 at 12:10
  • Does this answer your question? [DOUBLE vs DECIMAL in MySQL](https://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql) – Karl Knechtel Nov 04 '22 at 22:42

1 Answers1

1

The MySQL DOUBLE already is a 64-bit aka double precision floating point type just like Java's Double. Both can store the same values without loss of precision; a Java Double value can be stored exactly in a MySQL DOUBLE.

The alternate syntaxes for defining a precision or number of digits are optional. There is no obligation to use them when the standard FLOAT or DOUBLE are sufficient.

MisterMiyagi
  • 44,374
  • 10
  • 104
  • 119
  • So if the java application has a `double` with e.g. `0.0117340087990645` and the MySQL table is set with `Double` if I store the value from Java and `select` it back I will get `0.0117340087990645`? – Jim Nov 04 '22 at 12:32
  • @Jim Yes, so long as you don't (accidentally) change the type during reading/writing you will be able to get back the exact value you put in. – MisterMiyagi Nov 04 '22 at 12:39
  • By changing the type you mean casting? – Jim Nov 04 '22 at 12:42
  • 1
    @Jim Any kind of conversion, really. Say, loading the `DOUBLE` to a Java `float` before storing it as a `double`. – MisterMiyagi Nov 04 '22 at 13:36
  • Is the exact same bit pattern guaranteed to survive? What if the underlying hardware uses [an 80-bit or 128-bit (intermediate) representation](https://en.wikipedia.org/wiki/Extended_precision#IEEE_754_extended_precision_formats) instead of 64-bit (implicit conversions with change of the least significant bit might happen)? – Peter Mortensen Nov 04 '22 at 16:38
  • @PeterMortensen I doubt it. At least special values such as the various different nans are unlikely to be preserved in all ways of communicating with the DB. – MisterMiyagi Nov 04 '22 at 17:03
  • 1
    @PeterMortensen I don't think we care about intermediate representations for this purpose, since there would be no way to communicate the excess bits from the Java code to the database anyway - they only exist temporarily and at the hardware level. That said, I can't fathom any technical reason why the database *wouldn't* store the 64-bit value represented in the Java code, into the 64-bit wide database column, by copying bit for bit (without heed to the semantic interpretation of nans etc). – Karl Knechtel Nov 04 '22 at 22:44