[phil-story] Always Fighting 

IT_Expert/DataBase | Posted by 낫기법필 2016. 4. 4. 23:25

MySQL] DOUBLE vs DECIMAL in MySQL

[쥔장]========================================================

mysql을 사용하는 중에 double 형 값을 가지고 일반적인 계산 및 자릿수 올림도 하고 하는데, 계속 값이 머릿속과 계산기에 두들기는 것과는 다르게 나오는 현상이 있어서 찾아보니, 

제대로 된 계산 값을 가지기 위해서는 decimal로 쓰는 것이 맞는 것이였음.

하여 질의가 잘 정리된 것을 스크랩한다.

===============================================================


[http://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql]

up vote32down votefavorite
10

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 ?

  
shareimprove this question
   
Do you calculate taxable amounts in Java and then round them according to contract before storing them? For example, if you sell a $1.47 item and have 8.25% local sales tax, you might need to record $0.121275 in tax. I am wondering in what form you are storing this kind of field in the DB, and whether you are rounding to $0.12 before you store (or rounding up to $0.13, depending on your locale). – rajah9 Jul 26 '11 at 14:25
   
Yes, we calculate taxes in Java, and we store the item's price, the tax amount rounded to the 4th decimal and the total price rounded to the 2nd decimal. So in your example a row would contain 1.47, 0.1213 and 1.59. The 8.25% is stored somewhere else as 0.08250000 and is not repeated for each sales. – user327961 Jul 26 '11 at 14:54 
up vote20down voteaccepted

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.

shareimprove this answer
7 
Well this is not a technical answer to the case, but the point that makes me think the most is I personally would not trust financial data that uses floating point math. Most certainly many others would not 100% trust those data even if I spent a week trying to proove it safe in our usecases, and they would be right to doubt. Not having the client's trust in an audit is indeed an issue and is a good argument to recommend the switch from DOUBLE to DECIMAL. – user327961 Jul 26 '11 at 17:31
up vote15down vote

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;
+------+-------------------+------+
| 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)
shareimprove this answer
   
"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=78.8." This has an error, the result is "a = 76.80000000000001, b = 76.8", as shown in the SQL result. The error is the 1 on the very end, but this is so small and is just a result of double encoding using binary not decimal. – markwatson Mar 26 '14 at 18:02 
   
@markwatson it was a typo, you are correct. The sum of b is of course 76.8, I corrected it now. – broadbandMar 5 '15 at 11:57
up vote11down vote

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.

shareimprove this answer
4 
I just tried this: CREATE TABLE IF NOT EXISTS exact ( n decimal(5,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; I inserted data like this: insert into exact (n) values(4.389999999993); there was a warning ofcourse because of the rounding. But it stored 4.39 not 4.38 like you wrote. I'm using mySQL 5.5.28-log – broadband Feb 1 '13 at 10:14 
2 
Without the use of exact(), the inserted value is truncated, not rounded. – RandomSeed May 15 '13 at 12:22 
3 
The truncation behavior is documented in the MySQL manual: "When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)" I'd recommend explicitly specifying the rounding behavior using ROUND() – Stephen Jul 5 '13 at 22:06 
up vote0down vote

"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.

shareimprove this answer
up vote0down vote

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.51in 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.

shareimprove this answer
2 
"Your big question is: does anybody care if certain reports are off by a penny?" And if no: head to your box for writing a virus :P (re Office Space) Sorry, I just couldn't help it. – Halil Özgür Jun 14 '12 at 11:33



티스토리툴바