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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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  amp  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  amp  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  amp  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   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 mln USD     35 Quadrylion ZWL      10 6      35   10 15    35   10 21   we need          2 digits to store  35    21 digits to store the zeros   4 digits to the right of decimal point      this makes decimal 27 4  which costs us 15 bytes for each entry   we may add one more digit on the left at no expense - we have decimal 28 4  for 15 bytes    Now we can store 10 mln USD transaction expressed in Zimbabwean dollars  or secure from another strike of hiperinflation  which hopefully won t happen

User · Answer

super late entry but GAAP is a good rule of thumb    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   Source  Best datatype to store monetary value in MySQL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Best data type for storing currency values in a MySQL database

Examples related to mysql

Examples related to sql

Examples related to sqldatatypes