mysql货币类型_在MySQL中存储货币值的最佳数据类型

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.