Which Data Types Should You Use for Money in Java & MySQL? Best Practices

01 – Avoid Using VARCHAR for Monetary Amounts

Although VARCHAR is often used because it easily stores any format, it leads to low performance and cannot enforce numeric validation, making data integrity risky. Instead, choose BIGINT or DECIMAL (NUMERIC) to store monetary values.

02 – Prohibit Float and Double Types

Floating‑point types float and double suffer from precision loss, causing rounding errors that are unacceptable in financial calculations. They should be strictly avoided for monetary amounts.

03 – Long (Java) and BIGINT (MySQL) Usage Advice

When the smallest unit is a cent, converting amounts to integer cents allows simple arithmetic and easy integration with third‑party payment systems. Advantages include faster computation and lower storage overhead.

Suitable for systems where only cent precision is required.

Problems arise if higher precision (e.g., 4‑6 decimal places) is needed or if precision requirements change over time.

Interfacing with external systems that use float/double reintroduces precision issues.

Recommendations:

Use Long/BIGINT for payment or settlement systems that only need cent precision.

For scenarios demanding higher or variable precision (e.g., inventory systems), switch to BigDecimal (Java) and DECIMAL (NUMERIC) (MySQL).

04 – BigDecimal (Java) and Decimal (Numeric) Usage Advice

When precise decimal arithmetic is required, BigDecimal and DECIMAL provide high‑precision calculations and avoid the rounding errors of floating‑point types.

Construct BigDecimal objects using a String or BigDecimal.valueOf to prevent precision loss from double literals:

BigDecimal bd1 = new BigDecimal("0.1");
BigDecimal bd2 = BigDecimal.valueOf(0.1);

Also, explicitly set an appropriate rounding mode when performing operations to ensure consistent results.