DOUBLE vs DECIMAL in MySQL

OK, so I know there are tons of articles stating I shouldn't use DOUBLE to store money on a MySQL database, or I'll end up with tricky precision bugs. The point is I am not designing a new database, I am ask to find way to optimise an existing system. The newer version contains 783 DOUBLE typed columns, most of them used to store money or formula to compute money amount.

So my first opinion on the subject was I should highly recommend a conversion from DOUBLE to DECIMAL in the next version, because the MySQL doc and everybody say so. But then I couldn't find any good argument to justify this recommandation, for three reasons :

• We do not perform any calculation on the database. All operations are done in Java using BigDecimal, and MySQL is just used as a plain storage for results.
• The 15 digits precision a DOUBLE offers is plenty enough since we store mainly amounts with 2 decimal digits, and occasionaly small numbers wit 8 decimal digits for formula arguments.
• We have a 6 years record in production with no known issue of bug due to a loss of precision on the MySQL side.

Even by performing operations on a 18 millons rows table, like SUM and complex multiplications, I couldn't perform a bug of lack of precision. And we don't actually do this sort of things in production. I can show the precision lost by doing something like

SELECT columnName * 1.000000000000000 FROM tableName;

But I can't figure out a way to turn it into a bug at the 2nd decimal digit. Most of the real issues I found on the internet are 2005 and older forum entries, and I couldn't reproduce any of them on a 5.0.51 MySQL server.

So as long as we do not perform any SQL arithmetic operations, which we do not plan to do, are there any issue we should expect from only storing and retreiving a money amount in a DOUBLE column ?

Solution 1:

Actually it's quite different. DOUBLE causes rounding issues. And if you do something like 0.1 + 0.2 it gives you something like 0.30000000000000004. I personally would not trust financial data that uses floating point math. The impact may be small, but who knows. I would rather have what I know is reliable data than data that were approximated, especially when you are dealing with money values.

Solution 2:

The example from MySQL documentation http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html (i shrink it, documentation for this section is the same for 5.5)

mysql> create table t1 (i int, d1 double, d2 double);

mysql> insert into t1 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);

mysql> select
         i,
         sum(d1) as a,
         sum(d2) as b
       from
         t1
       group by
         i
       having a <> b; -- a != b

+------+-------------------+------+
| i    | a                 | b    |
+------+-------------------+------+
|    2 | 76.80000000000001 | 76.8 |
+------+-------------------+------+
1 row in set (0.00 sec)

Basically if you sum a you get 0-13.2+59.6+30.4 = 76.8. If we sum up b we get 0+0+46.4+30.4=76.8. The sum of a and b is the same but MySQL documentation says:

A floating-point value as written in an SQL statement may not be the same as the value represented internally.

If we repeat the same with decimal:

mysql> create table t2 (i int, d1 decimal(60,30), d2 decimal(60,30));
Query OK, 0 rows  affected (0.09 sec)

mysql> insert into t2 values (2, 0.00  , 0.00),
                             (2, -13.20, 0.00),
                             (2, 59.60 , 46.40),
                             (2, 30.40 , 30.40);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select
         i,
         sum(d1) as a,
         sum(d2) as b
       from
         t2
       group by
         i
       having a <> b;

Empty set (0.00 sec)

The result as expected is empty set.

So as long you do not perform any SQL arithemetic operations you can use DOUBLE, but I would still prefer DECIMAL.

Another thing to note about DECIMAL is rounding if fractional part is too large. Example:

mysql> create table t3 (d decimal(5,2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t3 (d) values(34.432);
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t3;
+-------+
| d     |
+-------+
| 34.43 |
+-------+
1 row in set (0.00 sec)

Solution 3:

We have just been going through this same issue, but the other way around. That is, we store dollar amounts as DECIMAL, but now we're finding that, for example, MySQL was calculating a value of 4.389999999993, but when storing this into the DECIMAL field, it was storing it as 4.38 instead of 4.39 like we wanted it to. So, though DOUBLE may cause rounding issues, it seems that DECIMAL can cause some truncating issues as well.