CODEWITHSUNDEEP
mysqlsqldatatypescurrency
Mohammad Saberi
Mohammad Saberi

Reputation: 13176

Best data type to store money values in MySQL

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

Upvotes: 368

Views: 404496

Answers (14)

juergen d
juergen d

Reputation: 204904

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
  • The max possible number in this example would be 9999999999999.99

See MySQL Numeric Types:

These types are used when it is important to preserve exact precision, for example with monetary data.

Upvotes: 503

bizwiz
bizwiz

Reputation: 349

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

Upvotes: 14

at54321
at54321

Reputation: 11846

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.

Upvotes: 2

user8588978
user8588978

Reputation:

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/

Upvotes: 8

Cardinal
Cardinal

Reputation: 2195

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.

Upvotes: 1

digitalstraw
digitalstraw

Reputation: 423

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

Upvotes: 5

antak
antak

Reputation: 20869

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.

Upvotes: 13

Deepesh
Deepesh

Reputation: 840

Try using

Decimal(19,4)

this usually works with every other DB as well

Upvotes: 4

auntyellow
auntyellow

Reputation: 2573

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

Upvotes: 0

NullPoiиteя
NullPoiиteя

Reputation: 57332

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

Upvotes: 94

Svetoslav
Svetoslav

Reputation: 4686

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.

Upvotes: 29

Chagbert
Chagbert

Reputation: 783

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

Upvotes: 5

Dinesh P.R.
Dinesh P.R.

Reputation: 7266

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.

Upvotes: 45

david.ee
david.ee

Reputation: 229

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.

Upvotes: 19

Related Questions