Best data type to store money values in MySQL

Question

I want to store many records in a MySQL database  All of them contains money values  But I don t know how many digits will be inserted for each one  Which data type do I have to use for this purpose  VARCHAR or INT  or other numeric data types

User · Answer

I prefer to use BIGINT  and store the values in by multiply with 100  so that it will become integer     For e g   to represent a currency value of 93 49  the value shall be stored as 9349  while displaying the value we can divide by 100 and display  This will occupy less storage space      Caution    Mostly we don t perform currency   currency multiplication  in case if we are doing it then divide the result with 100 and store  so that it returns to proper precision

User · Answer

Indeed this relies on the programmer s preferences  I personally use  numeric 15 4  to conform to the Generally Accepted Accounting Principles  GAAP

User · Answer

At the time this question was asked nobody thought about Bitcoin price  In the case of BTC  it is probably insufficient to use DECIMAL 15 2   If the Bitcoin will rise to  100 000 or more  we will need at least DECIMAL 18 9  to support cryptocurrencies in our apps   DECIMAL 18 9  takes 12 bytes of space in MySQL  4 bytes per 9 digits

User · Answer

You can use DECIMAL or NUMERIC both are same      The DECIMAL and NUMERIC types store exact numeric data values  These types are used when it is important to preserve exact precision  for example with monetary data  In MySQL  NUMERIC is implemented as DECIMAL  so the following remarks about DECIMAL apply equally to NUMERIC    MySQL   i e  DECIMAL 10 2         Good read

User · Answer

We use double    gasp   Why   Because it can represent any 15 digit number with no constraints on where the decimal point is   All for a measly 8 bytes   So it can represent    0 123456789012345 123456789012345 0      and anything in between   This is useful because we re dealing with global currencies  and double can store the various numbers of decimal places we ll likely encounter   A single double field can represent 999 999 999 999 999s in Japanese yens  9 999 999 999 999 99s in US dollars and even 9 999 999 99999999s in bitcoins  If you try doing the same with decimal  you need decimal 30  15  which costs 14 bytes   Caveats  Of course  using double isn t without caveats   However  it s not loss of accuracy as some tend to point out   Even though double itself may not be internally exact to the base 10 system  we can make it exact by rounding the value we pull from the database to its significant decimal places   If needed that is    e g  If it s going to be outputted  and base 10 representation is required    The caveats are  any time we perform arithmetic with it  we need to normalize the result  by rounding it to its significant decimal places  before    Performing comparisons on it  Writing it back to the database    Another kind of caveat is  unlike decimal m  d  where the database will prevent programs from inserting a number with more than m digits  no such validations exists with double   A program could insert a user inputted value of 20 digits and it ll end up being silently recorded as an inaccurate amount

User · Answer

If GAAP Compliance is required or you need 4 decimal places   DECIMAL 13  4  Which supports a max value of    999 999 999 9999  Otherwise  if 2 decimal places is enough  DECIMAL 13 2   src  https   rietta com blog best-data-types-for-currencymoney-in

User · Answer

Since money needs an exact representation don t use data types that are only approximate like float  You can use a fixed-point numeric data type for that like  decimal 15 2     15 is the precision  total length of value including decimal places  2 is the number of digits after decimal point   See MySQL Numeric Types      These types are used when it is important to preserve exact precision  for example with monetary data

User · Answer

It depends on your need   Using DECIMAL 10 2  usually is enough but if you need a little bit more precise values you can set DECIMAL 10 4    If you work with big values replace 10 with 19

User · Answer

Multiplies 10000 and stores as BIGINT  like  Currency  in Visual Basic and Office  See https   msdn microsoft com en-us library office gg264338 aspx

User · Answer

Try using  Decimal 19 4    this usually works with every other DB as well

User · Answer

Storing money as BIGINT multiplied by 100 or more with the reason to use less storage space makes no sense in all  normal  situations     To stay aligned with GAAP it is sufficient to store currencies in DECIMAL 13 4  MySQL manual reads that it needs 4 bytes per 9 digits to store DECIMAL     https   dev mysql com doc refman 8 0 en precision-math-decimal-characteristics html  DECIMAL 13 4  represents 9 digits   4 fraction digits  decimal places     4   2 bytes   6 bytes compare to 8 bytes required to store BIGINT

User · Answer

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

[mysql] Best data type to store money values in MySQL

Examples related to mysql

Examples related to sqldatatypes

Examples related to currency