353

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)?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
  • 21
    `deimal(10,2)` is what i use ... you can adjust the values depending on size expected – Manse Oct 23 '12 at 12:27
  • 9
    Related question is [Best Data Type for Currency](http://stackoverflow.com/q/628637/4519059) ;). – shA.t Apr 28 '15 at 10:10

14 Answers14

485

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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 3
    what could be the difference between decimal and numeric data type for this case? – Emilio Gort Feb 06 '14 at 20:04
  • 65
    In MySQL `decimal` and `numeric` are the same. – juergen d Feb 27 '14 at 11:29
  • 32
    I personally use ```numeric(19,4)``` for financial records that gives you a better hand to play and adopt new requests easily. – YahyaE Jul 22 '15 at 11:40
  • 12
    I agree with YahyaE, more decimals is better. There are some currencies that typically use 3 decimal places, such as the Bahraini, Jordanian, or Kuwaiti Dinars, so you need at least 3. Four or five is better. – Edwin Hoogerbeets May 24 '16 at 20:35
  • 3
    @EdwinHoogerbeets Not being an accountant... but running a small biz in the UK... I remember reading somewhere a long time ago that currency figures should be stored to 4 decimals even for £, $, etc. so that certain calculations might actually *use* the last 2 decimal places for certain obscure accounting contexts. Wd need an accountant to confirm/refute. – mike rodent Aug 03 '17 at 19:43
  • Some currencies [have 3 digits](https://en.wikipedia.org/wiki/ISO_4217#Active_codes) after the decimal point. If you want to handle [hyper-inflated currencies like Zimbabwe dollars](https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe), the exchange rate was like 10³⁰ to 1 USD in 2009 so 15 digits before the decimal point would not be enough. – Boris Verkhovskiy Mar 08 '22 at 22:34
  • In terms of storage, the suggested `DECIMAL(15,2)` would take 7 bytes in MySQL. See [this](https://stackoverflow.com/a/76068566/15602349) for more on how storage size for `DECIMAL`/`NUMERIC` is calculated. – at54321 Apr 21 '23 at 20:37
94

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)

Example settings

Good read

NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143
  • I am using decimal(10,2) for my money value, however when I put something like 867,000.00 it gets saved as 867. what am I doing wrong? – codeinprogress Jan 16 '17 at 14:43
  • @codeinprogress you have a comma in your money value...if it goes into the database that same way. Mysql will truncate starting from the comma position – Chidozie Duru Dec 24 '21 at 13:55
41

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Dinesh P.R.
  • 6,936
  • 6
  • 35
  • 44
  • I remember being told a similar thing by a professor on my Computer Systems university course. I was taught the most precise way is to store in pennies (or cent) by multiply by 100 and saving as an Integer and dividing by 100 to display it to the user. I guess this has benefits in terms of accuracy and performance of the database system. – LondonAppDev Feb 12 '15 at 08:39
  • I think, This is a `Scale Removing Method` that removes the worrying of changing the scale by the country -e.g. happens when a 1020(X).03(x) changes to 10(x).2003(x)- that Just UI should changed ;). – shA.t Apr 28 '15 at 10:06
  • 21
    What is the advantage over `DECIMAL`? You create a need to translate pennies to dollars, and woe if you forget it at some point. –  Apr 29 '15 at 20:03
  • 1
    Space is the only advantage, but yes we need to be more careful when we are using this feature. – Dinesh P.R. Aug 11 '15 at 04:54
  • 4
    In case it's not obvious: be wary of using the scale removing method if you store money in fractional cents (e.g., `$0.005` or `$0.12345`) because they will not reduce to an integer after multiplying by 100. If you know the precision of the values it's clear the best option is to use `DECIMAL`. But if you don't know the precision (as in my examples) then…would `FLOAT` be appropriate? – Quinn Comendant Sep 05 '15 at 02:00
  • 3
    An advantage of this method comes when using a language like JavaScript that uses IEEE-754 to store floating point numbers. This specification does not guarantee that 0.1 + 0.2 === 0.3 is true. Storing currency as an integer gives certainty that your application will not incur that kind of error. This may not be the best solution though. I arrived at this page while researching solutions and I'm not done yet. – Gary Ott Jun 02 '17 at 09:00
  • I like this approach, but sometimes, the system you're working on do not demand such precision, so often I go for decimal(10,2) for the sake of productivity. – Ricardo Vigatti Sep 01 '17 at 21:40
  • This is a solution I have used when using programming languages that only offered the choice between 4-byte integers and double-precision binary floating point. In this case I would use the floating point to hold integer values (i.e. cents for U.S. currency) because of its greater precision. But it seems senseless *not* to use decimal fixed point math when it is available. – Booboo Jul 24 '19 at 19:24
  • A danger with this method is that currencies sometimes change their number of decimals. If you use this method you need to store the number of decimals separately. Better to use `DECIMAL`. – Christoffer Hammarström Dec 20 '22 at 13:18
28

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.

Svetoslav
  • 4,686
  • 2
  • 28
  • 43
17

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.

david.ee
  • 209
  • 2
  • 2
  • 6
    If you're going to take Bitcoin, you're going to need 8 decimal places, although most wallets go to mBTC which is 3 http://en.wikipedia.org/wiki/Bitcoin – Christian Aug 21 '14 at 17:49
  • Don't think this answer is true. http://opendata.stackexchange.com/a/10348/13983 @david.ee got a source for that? – Evan Carroll Dec 28 '16 at 22:25
  • @EvanCarroll let me answer for david.ee. I think this article may be the source https://rietta.com/blog/2012/03/03/best-data-types-for-currencymoney-in/ – naXa stands with Ukraine Mar 14 '18 at 23:25
  • @naXa the link does not quote anything from any source that support's the claim of using 13,4 for GAAP. All you did was link to an article that makes the same unsubstantiated claim. – iheanyi Nov 08 '18 at 23:32
13

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:

  1. Performing comparisons on it.
  2. 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.

antak
  • 19,481
  • 9
  • 72
  • 80
  • First time I've seen an answer like this, interesting. Q: If I write a float like 1.41 to the database and for some reason I need to multiply it by some huge number in mysql, like 1.000.000.000.000. Will the rounded result exactly be: 1.410.000.000.000? – roelleor May 20 '19 at 14:25
  • @roelleor For the result to be exactly 1,410,000,000,000 (comma as thousands separator) the input is assumed to be `1.410000000000` (twelve significant decimal places), but multiplying that by 1,000,000,000,000 (which is 13 significant digits left of decimal point) means we are working with at least a combined 25 digits of significance. This far surpasses the 15 available for a double, so design-wise I think it would be very broken. – antak May 20 '19 at 16:20
  • Then you sum up the total cost of buying 3 items with a cost of `0.99` each, and end up with a total sum of `2.9699999999999998`. – Christoffer Hammarström Dec 20 '22 at 13:27
  • @ChristofferHammarström `2.9699999999999998` is way more than 15 digits. You need to know how many digits are significant your currency and round to it. If it's the full 15, it's going to be `2.97000000000000`, but you started on `0.99` so it's probably two so `2.97`. For details, see under caveats. – antak Dec 21 '22 at 03:53
  • So you can either remember to always round and hope that you don't have enough compounded errors, or you can use a data type which doesn't have the problem. – Christoffer Hammarström Dec 21 '22 at 12:09
  • @ChristofferHammarström Any 15 digit number can be represented precisely with a 64-bit float. Perform arithmetics and you'll still have an accurate result within those 15 digits. I'm not sure what compounded errors you're thinking of that will break this. If the result of arithmetics needs to be over 15 digits then it's going to break, just like I can't put a 16 digit number in a `decimal(15)`. You don't need to round during the arithmetics, but seeing `2.9699999999999998` printed to the console will be a nature of floats, because the stringifier doesn't know how many digits you want. – antak Dec 21 '22 at 15:49
  • I'm being asked to support `type(22,2)` (where `type` is TBD). Any value that fills that loses accuracy when stored in `double(22,2)`. You're also wrong about "A program could insert a user inputted value of 20 digits and it'll end up silently recorded as an inaccurate amount." In MySQL, it throws an error 1264. – randy Feb 23 '23 at 12:25
12

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 8 bytes of space in MySQL (4 bytes per 9 digits).

bizwiz
  • 329
  • 3
  • 5
7

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/

Dale K
  • 25,246
  • 15
  • 42
  • 71
5

Indeed this relies on the programmer's preferences. I personally use: numeric(15,4) to conform to the Generally Accepted Accounting Principles (GAAP).

shA.t
  • 16,580
  • 5
  • 54
  • 111
Chagbert
  • 722
  • 7
  • 16
  • 13
    It has nothing whatsoever to do with "programmer's preferences" or what you 'personally use'. It is dictated by the problem domain, which requires a decimal radix. This is not a matter in which the programmer gets to exercise his own personal preferences. – user207421 Apr 28 '15 at 10:50
4

Try using

Decimal(19,4)

this usually works with every other DB as well

Deepesh
  • 820
  • 1
  • 14
  • 32
4

Storing money as BIGINT multiplied by 100 or more with the reason to use less storage space makes no sense in all "normal" situations.

digitalstraw
  • 403
  • 3
  • 7
1

There are 2 valid options:

  1. use integer amount of currency minor units (e.g. cents)
  2. represent amount as decimal value of the currency

In both cases you should use decimal data type to have enough significant digits. The difference can be in precision:

  • even for integer amount of minor units it's better to have extra precisions for accumulators (account for accumulating 10% fees from 1-cent operations)
  • different currencies have different number of decimals, cryptocurrencies have up to 18 decimals
  • The number of decimals can change over time due to inflation

Source and more caveats and facts.

Cardinal
  • 1,321
  • 1
  • 11
  • 5
1

Use DECIMAL (or NUMERIC - it's the same in MySQL).

Do NOT use DOUBLE or FLOAT (especially FLOAT). Those are floating-point numbers with binary precision. (In Oracle, the NUMBER type is also a floating-point but with decimal precision, which is fine for monetary operations).

With DECIMAL you need to specify the total precision and the fractional part. For example:

  • DECIMAL(10,2) can store values between -99,999,999.99 and 99,999,999.99.
  • DECIMAL(19,4) can store values between -999,999,999,999,999.9999 and 999,999,999,999,999.9999.

Choose a range that will be big enough for your case, but be aware that the bigger the range, the more storage would be used. In MySQL, DECIMAL(10,2) will always use 5 bytes and DECIMAL(19,4) will always use 9 bytes, regardless of the actual values you store. See this for more on how DECIMAL's storage size is calculated.

at54321
  • 8,726
  • 26
  • 46
0

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

auntyellow
  • 2,423
  • 2
  • 20
  • 47