DOUBLE vs DECIMAL in MySQL

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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 gt  create table t1  i int  d1 double  d2 double    mysql gt  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 gt  select          i           sum d1  as a           sum d2  as b        from          t1        group by          i        having a  lt  gt  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 gt  create table t2  i int  d1 decimal 60 30   d2 decimal 60 30    Query OK  0 rows  affected  0 09 sec   mysql gt  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 gt  select          i           sum d1  as a           sum d2  as b        from          t2        group by          i        having a  lt  gt  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 gt  create table t3  d decimal 5 2    Query OK  0 rows affected  0 07 sec   mysql gt  insert into t3  d  values 34 432   Query OK  1 row affected  1 warning  0 10 sec   mysql gt  show warnings   ------- ------ ----------------------------------------    Level   Code   Message                                   ------- ------ ----------------------------------------    Note    1265   Data truncated for column  d  at row 1    ------- ------ ----------------------------------------  1 row in set  0 00 sec   mysql gt  select   from t3   -------    d        -------    34 43    -------  1 row in set  0 00 sec

User · Answer

are there any issue we should expect from only storing and retreiving a money amount in a DOUBLE column     It sounds like no rounding errors can be produced in your scenario and if there were  they would be truncated by the conversion to BigDecimal   So I would say no   However  there is no guarantee that some change in the future will not introduce a problem

User · Answer

From your comments      the tax amount rounded to the 4th decimal and the total price rounded   to the 2nd decimal    Using the example in the comments  I might foresee a case where you have 400 sales of  1 47  Sales-before-tax would be  588 00  and sales-after-tax would sum to  636 51  accounting for  48 51 in taxes   However  the sales tax of  0 121275   400 would be  48 52   This was one way  albeit contrived  to force a penny s difference   I would note that there are payroll tax forms from the IRS where they do not care if an error is below a certain amount  if memory serves   0 50    Your big question is  does anybody care if certain reports are off by a penny  If the your specs say  yes  be accurate to the penny  then you should go through the effort to convert to DECIMAL   I have worked at a bank where a one-penny error was reported as a software defect  I tried  in vain  to cite the software specifications  which did not require this degree of precision for this application   It was performing many chained multiplications   I also pointed to the user acceptance test   The software was verified and accepted    Alas  sometimes you just have to make the conversion  But I would encourage you to A  make sure that it s important to someone and then B  write tests to show that your reports are accurate to the degree specified

[mysql] DOUBLE vs DECIMAL in MySQL

Examples related to mysql

Examples related to double

Examples related to decimal