CODEWITHSUNDEEP
javamysql
zhuguowei
zhuguowei

Reputation: 8487

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

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?

Upvotes: 2

Views: 5482

Answers (2)

Andreas
Andreas

Reputation: 159106

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.

Upvotes: 1

user3707125
user3707125

Reputation: 3484

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.

Upvotes: 0

Related Questions