3

I'm working on an application where we have some services that requires a monetary value to calculate budgets for services. Currently we are using columns DECIMAL(10,2).

While analysing some of the generated budgets I've noticed some of them returns values like 180.0000000024.

Reading on other questions, I've only found the same type of answers, to go with decimals. In a few cases I saw a recomendation to use INT (or similar) instead. The reason is because the calculations will be more precise.

Example: $64.58 + $348.99 would be calculated like 6458 + 34899 and stored the result 41357 and later on converted to 413.57.

So it left mw with this questions, on which is the best way to store currency values on the database. Is DECIMAL really the best way? How much of a problem those extra decimals values can be a problem? Is INT (or similar) type going to improve something?

1
  • DECIMAL(19,4) is the standard in the Java world Commented Nov 23, 2017 at 23:08

1 Answer 1

4

Do not use FLOAT or DOUBLE. If it is a single currency, then use DECIMAL(m,n) with m and n suitably chosen. If different currencies are stored in the same column, values for m and n become messy. And you can run into rounding issues -- because of different laws.

DECIMAL goes through a package that does INT-like stuff transparently. DECIMAL(19,4) will be stored in

6 digits in (effectively) a `MEDIUMINT` (high-order digits)
9 digits in (effectively) a `INT`
4 digits in (effectively) a `SHORTINT`  (after the decimal point)
1
  • A rule of thumb: DECIMAL(m,n) takes about m/2 bytes. In this example (3+4+2) = 9 bytes ~= 19/2.
    – Rick James
    Commented Oct 3, 2018 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.