what's the difference of double in mysql and java?

Question

Have a table,

CREATE TABLE `double_test` (
  `price` double(11,2) DEFAULT NULL
)

and there are two records:

select * from double_test;
+---------+
| price   |
+---------+
| 1100.00 |
| 1396.32 |
+---------+

and sum them:

select sum(price) from double_test;
+------------+
| sum(price) |
+------------+
|    2496.32 |
+------------+

And sum the two double in Java ,

System.out.println(1100.00 + 1396.32); //2496.3199999999997
System.out.println((1100.00 + 1396.32) == 2496.32); //false

So both are double why in mysql could get correct result? and what's the difference of double type in mysql and java?

In java if use BigDecimal could get correct result, e.g.

     System.out.println(BigDecimal.valueOf(1100.00).add(BigDecimal.valueOf(1396.32)).equals(BigDecimal.valueOf(2496.32)));//true

Is actually double in mysql equal to BigDecimal in Java?

Have a look at the explanation in this answer: http://stackoverflow.com/a/3730040/2002257 - it will give you some understanding into how doubles work in Java. As for mysql - I would guess it doesn't implement them the same. — jheimbouch, Feb 01 '16 at 02:16
Thanks! but I want to know the difference of double type between mysql and java. — zhuguowei, Feb 01 '16 at 02:22
Possible duplicate of [Why not use Double or Float to represent currency?](http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency) — Jia Jian Goi, Feb 01 '16 at 02:48
[MySQL Floating-point types](http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html) — user207421, Feb 01 '16 at 03:52

score 1 · Accepted Answer · answered Feb 01 '16 at 02:53

what's the difference of double type in mysql and java?

There is no difference between MySQL DOUBLE and Java Double.

There is however a difference when using MySQL's DOUBLE(m,n), since that constrains the value (see below).

Is actually double in mysql equal to BigDecimal in Java?

No, the MySQL data type DECIMAL(m,n) is closer in functionality to Java's BigDecimal, except that MySQL constrains the value (see below).

Value Constraints

Java's double stores the full precision supported by a IEEE 64-bit double, and BigDecimal is entirely unconstrained.

In contrast, MySQL's DOUBLE(m,n) and DECIMAL(m,n) constrains the value to m total digits, with n digits after the decimal point.

As described for DOUBLE(m,n):

MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

The decimal constraint n is also used to format the value when displayed.

As for the calculation of 1100.00 + 1396.32, MySQL says:

the precision of the result is the precision of the operand with the maximum precision.

This would mean that the result of the addition is rounded to 2 decimals, which makes a difference when using the approximate numeric data type DOUBLE. Java doesn't round the result, so may calculate a result that is slightly different out at the last bit.

So if in your business only support two digits after decimal point, like 1.23. So in mysql double(m,2) is (safe) enough, no need have to use decimal(m,2). Am I right? — zhuguowei, Feb 02 '16 at 02:24
In mysql's `Double(m,n)`, what is the maximum value `m` and `n` can take? — Naanavanalla, Aug 23 '17 at 09:12
can I use java BigDecimal with MySQL double, is it compatible? — Fakipo, Oct 09 '20 at 18:07

score 0 · Answer 2 · answered Feb 01 '16 at 02:23

and what's the difference of double type in mysql and java?

Shortly: Java has no clue about (11,2) part in your MySQL's declaration double(11,2).

The difference is that java's double is a general purpose type, that may store an arbitrary value between 4.9e-324 and 1.7e308, while MySQL creates a different double type for every declaration that includes (digits in total, digits after the decimal point) part.

what's the difference of double in mysql and java?

2 Answers2