When developing payment or e-commerce systems, have you ever considered using FLOAT or DOUBLE for database amount fields?

If so, stop right there! Your system might be secretly leaking money.

Why are floating-point numbers forbidden in financial systems? A seemingly insignificant precision error, accumulating over massive transaction volumes and time, could lead to irreversible disasters.

So, what exactly should we use to store money?

Why is FLOAT Toxic for Financial Systems?

In the computer world, numbers are represented in binary. FLOAT (floating-point numbers), when representing certain decimal fractions, is actually an “approximation.” It’s like trying to cut a delicate cake with a rough chainsaw; no matter how careful you are, some crumbs will always fall off the edges.

The classic example is: 0.1 + 0.2 often does not equal 0.3 in a computer. If you’re processing millions of transactions, these tiny errors of “0.00000000000000004” will accumulate, and the ledgers will never balance. Remember:

When it comes to money, any “approximation” is a disaster.

The Accountant’s Precise Ledger: The Advantages of DECIMAL

If you want an exact solution where “what you see is what you get,” DECIMAL is the database’s native fixed-point number and the industry standard.

DECIMAL is like the exquisite ledger in an accountant’s hands; it precisely separates integers and decimals, ensuring 0.1 + 0.2 absolutely equals 0.3.

The Industry Golden Ratio: DECIMAL(19, 4)

We generally recommend using DECIMAL(19, 4):

• 19: Represents a total capacity of 19 digits (precision).
• 4: Denotes retaining 4 digits after the decimal point.

Why keep 4 decimal places? Because during calculations for interest, tax rates, or exchange rates, intermediate steps often yield more than 2 decimal places. Reserving 2 extra buffer digits enhances calculation accuracy, and you can simply round off according to business needs at the end.

This capacity is even large enough for you to buy several Earths’ total GDP!

Is It Enough for Real-World Financial Scenarios?

Taking DECIMAL(19, 4) as an example:

• Integer digits: 15 digits
• Maximum amount: 999,999,999,999,999
• USD conversion: Approx. 999 Trillion USD

DECIMAL(19, 4) can accommodate numbers far exceeding the total global wealth, perfectly sufficient for the vast majority of financial systems.

Maximum DECIMAL Precision Limits Supported by Major Databases

The Arcade Token Machine: The BIGINT Smallest Unit Method

If you are pursuing ultimate performance, or if your system has ultra-high concurrency demands like Stripe or Alipay, then BIGINT (integer storage method) might be your top choice.

This approach is like an arcade’s token machine: no matter how much money you insert, the machine converts it into the “smallest unit” for storage. For example:

• $100.50 USD → Stored as 10050 (cents)
• 100 TWD → Stored as 100 (dollars)

Why Choose BIGINT?

However, the drawback is poorer readability. When you open the database and see 10050, you must automatically divide it by 100 in your head (or your code).

The Ultimate Showdown: How to Choose?

To decide which one to use, we can consider “query frequency” and “system scale”:

Pragmatic Recommendations

Summary

In short, no matter which one you choose, never, absolutely, permanently forbidden to use FLOAT to store money! Choosing the correct field type guarantees your system remains rock-solid in financial calculations.