patternsqlMinor
Best way to store currency values on MySql database
Viewed 0 times
bestwaystoredatabasemysqlvaluescurrency
Problem
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
While analysing some of the generated budgets I've noticed some of them returns values like
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
Example:
So it left mw with this questions, on which is the best way to store currency values on the database. Is
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?Solution
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 in6 digits in (effectively) a `MEDIUMINT` (high-order digits)
9 digits in (effectively) a `INT`
4 digits in (effectively) a `SHORTINT` (after the decimal point)Code Snippets
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)Context
StackExchange Database Administrators Q#191605, answer score: 4
Revisions (0)
No revisions yet.