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

# sqldatatypes – Best data type to store money values in MySQL

## The Question :

*291 people think this question is useful*

*The Question Comments :*

`deimal(10,2)`

is what i use … you can adjust the values depending on size expected- Related question is Best Data Type for Currency ;).

## The Answer 1

*389 people think this answer is useful*

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

monetarydata.

## The Answer 2

*89 people think this answer is useful*

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

## The Answer 3

*35 people think this answer is useful*

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.

## The Answer 4

*27 people think this answer is useful*

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`

.

## The Answer 5

*16 people think this answer is useful*

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.

## The Answer 6

*6 people think this answer is useful*

Indeed this relies on the programmer’s preferences. I personally use: `numeric(15,4)`

to conform to the *Generally Accepted Accounting Principles ( GAAP)*.

## The Answer 7

*5 people think this answer is useful*

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

## The Answer 8

*4 people think this answer is useful*

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

## The Answer 9

*4 people think this answer is useful*

**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/

## The Answer 10

*3 people think this answer is useful*

Try using

Decimal(19,4)

this usually works with every other DB as well

## The Answer 11

*3 people think this answer is useful*

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`

. `DECIMAL(13,4)`

represents 9 digits + 4 fraction digits (decimal places) => 4 + 2 bytes = 6 bytes- compare to 8 bytes required to store
`BIGINT`

.

## The Answer 12

*0 people think this answer is useful*

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