Best data type for storing currency values in a MySQL database

Question

What is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type.

Avram

Possible duplicate of Best data type to store money values in MySQL – Washwoman

Answer 1

Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use "money" as it's non-standard.

Frances

A point about the size: according to MSDN (msdn.microsoft.com/en-us/library/ms187746.aspx), Decimal(10,4) and Decimal(19,4) both use 9 bytes of storage, so might as well spring for that extra 9 digits of scale. – Doorpost

The MSDN article is about SQL Server but the question is about MySQL. (I have met developers who think both are the same so best to be clear.) – Allyl

What is the benefit to using (19,4) instead of (19,2)? – Fandango

My benefit was this.. I needed all my table rows to equal a particular amount of money. Let's say that amount is $10.00. As new rows get added each row amount changes. If there are 3 rows in the table. 10 / 3 = 3.3333333333... but with only 2 decimals they are stored as 3.33. So when you sum those up, 3.33 + 3.33 + 3.33 = 9.99. We lost a penny! Gets even worse on a larger dataset. Store at 19,4 and sum your totals, then round the output to 19,2.. – Sweettempered

I came here thinking about international values, particularly EUR with commas. You should still use decimal/numeric: #54644722 – Herbage

Answer 2

The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things

( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html )

    DBASE: 10,5 (10 integer, 5 decimal)
    MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal)

Panzer

Link is now dead, unfortunately. – Selfregulated

@MarcoAurélioDeleu - I've changed the link to point to the page on the Wayback Machine, so you can see it as looked back in 2009. – Corridor

Answer 3

It is also important to work out how many decimal places maybe required for your calculations.

I worked on a share price application that required the calculation of the price of one million shares. The quoted share price had to be stored to 7 digits of accuracy.

Chromonema

It's a good point - especially in financial apps, "price" most certainly doesn't imply "money" – Seabrook

Answer 4

Assaf's response of

Depends on how much money you got...

sounds flippant, but actually it's pertinant.

Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacific Francs per night... something that was never anticpated when the database schema was designed 10 years ago.

Hydria

Which is why I recommended decimal (19,4). May sound like overkill, but you never know when you'll need to store a really large amount in that field. – Frances

@Kibbee: I wouldn't have agreed with you for our requirements until today. (For 6 years (11,4) was perfectly fine...) – Hydria

and if you want to handle hyper-inflated currencies like Zimbabwe dollars, the exchange rate hit like 10³⁰ to 1 USD in 2009 – Grochow

Answer 5

For accounting applications it's very common to store the values as integers (some even go so far as to say it's the only way). To get an idea, take the amount of the transactions (let's suppose $100.23) and multiple by 100, 1000, 10000, etc. to get the accuracy you need. So if you only need to store cents and can safely round up or down, just multiply by 100. In my example, that would make 10023 as the integer to store. You'll save space in the database and comparing two integers is much easier than comparing two floats. My $0.02.

Calenture

How do you store 6.125 (6 1/8)? – Pruett

I guess he would store it as an integer 6125. – Sandasandakan

How would this be better than DECIMAL? You'd need to be very careful about always translating pennies, mills, or millrays into dollars, at the appropriate times. – Showplace

I've read that performance is typically faster with INT than DECIMAL, even in the latest versions of MySQL. It's also easier when you need to bring these values into PHP or something and compare values. – Calenture

This is idea will work, but it's a very dated design. The only systems you would want to use integers with would be those that don't support precise number formats. 25 years ago, that was quite common. Today, nearly any business application or RDBMS suitable for a business application will support a precise number format such as SQL's decimal or C#'s decimal. I can imagine a system with such a volume of influx of financial data that it might still require integer-only math today, but such a system would not be designed by someone looking for an answer on SO. – Drobman

Answer 6

It depends on the nature of data. You need to contemplate it beforehand.

My case

• decimal(13,4) unsigned for recording money transactions
  • storage efficient (4 bytes for each side of decimal point anyway) 1
  • GAAP compliant
• decimal(19,4) unsigned for aggregates
  • we need more space for totals of multiple multi-billion transactions
  • semi-compliance with MS Currency data type won't hurt 2
  • it will take more space per record (11 bytes - 7 left & 4 right), but this is fine as there are fewer records for aggregates 1
• decimal(10,5) for exchange rates
  • they are normally quoted with 5 digits altogether so you could find values like 1.2345 & 12.345 but not 12345.67890
  • it is widespread convention, but not a codified standard (at least to my quick search knowledge)
  • you could make it decimal (18,9) with the same storage, but the datatype restrictions are valuable built-in validation mechanism

Why (M,4)?

• there are currencies that split into a thousand pennies
• there are money equivalents like "Unidad de Fermento", "CLF" expressed with 4 significant decimal places 3,4
• it is GAAP compliant

Tradeoff

• lower precision:
  • less storage cost
  • quicker calculations
  • lower calculation error risk
  • quicker backup & restore
• higher precision:
  • future compatibility (numbers tend to grow)
  • development time savings (you won't have to rebuild half a system when the limits are met)
  • lower risk of production failure due to insufficient storage precision

Compatible Extreme

Although MySQL lets you use decimal(65,30), 31 for scale and 30 for precision seem to be our limits if we want to leave transfer option open.

Maximum scale and precision in most common RDBMS:

            Precision   Scale
Oracle      31          31
T-SQL       38          38
MySQL       65          30
PostgreSQL  131072      16383

6, 7, 8, 9

Reasonable Extreme

2. Why (27,4)?
   • you never know when the system needs to store Zimbabwean dollars

September 2015 Zimbabwean government stated it would exchange Zimbabwean dollars for US dollars at a rate of 1 USD to 35 quadrillion Zimbabwean dollars 5

We tend to say "yeah, sure... I won't need that crazy figures". Well, Zimbabweans used to say that too. Not to long ago.

Let's imagine you need to record a transaction of 1 mln USD in Zimbabwean dollars (maybe unlikely today, but who knows how this will look like in 10 years from now?).

1. (1 mln USD) * (35 Quadrylion ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
2. we need:
   • 2 digits to store "35"
   • 21 digits to store the zeros
   • 4 digits to the right of decimal point
3. this makes decimal(27,4) which costs us 15 bytes for each entry
4. we may add one more digit on the left at no expense - we have decimal(28,4) for 15 bytes
5. Now we can store 10 mln USD transaction expressed in Zimbabwean dollars, or secure from another strike of hiperinflation, which hopefully won't happen

Equidistant

Answer 7

super late entry but GAAP is a good rule of thumb. Applying, DECIMAL(13,4) to currency fields should suffice.

If your application needs to handle money values up to a trillion then this should work: 13,2 If you need to comply with GAAP (Generally Accepted Accounting Principles) then use: 13,4

Usually you should sum your money values at 13,4 before rounding of the output to 13,2.

With MySQL, you can use either DECIMAL or NUMERIC data types as they store exact numeric data values.

Using 13,4 allows for $999,999,999.9999. According to, ISO 4217, only 2 countries use 4 decimal places (Chile and Uruguay).

Sources:

• ISO 4217 - Defines alpha and numeric codes for the representation of currencies.
• List of Circulating Currencies
• Best datatype to store monetary value in MySQL

Depew

Answer 8

You could use something like DECIMAL(19,2) by default for all of your monetary values, but if you'll only ever store values lower than $1,000, that's just going to be a waste of valuable database space.

For most implementations, DECIMAL(N,2) would be sufficient, where the value of N is at least the number of digits before the . of the greatest sum you ever expect to be stored in that field + 5. So if you don't ever expect to store any values greater than 999999.99, DECIMAL(11,2) should be more than sufficient (until expectations change).

If you want to be GAAP compliant, you could go with DECIMAL(N,4), where the value of N is at least the number of digits before the . of the greatest sum you ever expect to be stored in that field + 7.

Umeh

Answer 9

Though this may be late, but it will be helpful to someone else.From my experience and research I have come to know and accept decimal(19, 6).That is when working with php and mysql. when working with large amount of money and exchange rate

Thunder

Answer 10

Short answer: I would recommend using decimal with the precision according to your needs. Decimal with precision = 0 can be the option if you want to store the integer number of currency minor units (e.g. cents) and you have troubles handling decimals in your programming language.

To find out the needed precision you need to consider the following:

• Types of currencies you support (they can have different number of decimals). Cryptocurrencies have up to 18 decimals (ETH). The number of decimals can change over time due to inflation.
• Storing prices of small units of goods (probably as a result of conversion from another currency) or having accumulators can require using more decimals than are defined for a currency

Storing integer number of minimal units can lead to the need of rescaling values in the future if you need to change the precision. If you use decimals, it's much easier.

More details and caveats in the article.

Grievous