DOUBLE vs DECIMAL in MySQL: Revisiting the Precision Debate

It's a common refrain that DOUBLE should be avoided for storing monetary values in MySQL databases due to potential precision issues. However, a practical scenario raises questions about the validity of this recommendation.

Balancing Precision Concerns with Practical Considerations

The given scenario involves a legacy system with numerous DOUBLE columns for money storage. Despite the absence of SQL arithmetic operations, the concerns lie in the potential for precision loss during storage and retrieval.

Arguments Against Conversion

• No adverse effects: Despite six years of use, no precision-related bugs have been observed.
• Sufficient precision: DOUBLE's 15-digit precision accommodates the required decimal precision of 2-8 digits.
• External calculations: Monetary computations are performed in Java using BigDecimal, minimizing the impact of MySQL data formatting.

Arguments for Caution

• Rounding issues: DOUBLE uses floating-point math, which can introduce rounding errors. Operations like 0.1 0.2 may result in 0.30000000000000004.
• Potential precision loss: While it's unlikely that a loss of precision would occur in the given scenario, it remains a theoretical possibility.

Conclusion

In the context of the specific scenario, the arguments against converting DOUBLE to DECIMAL outweigh those for it. The absence of SQL arithmetic operations, sufficient precision, and a proven track record suggest that the risks of precision loss are minimal. However, it's important to note that for high-precision monetary computations or in databases subject to extensive SQL arithmetic operations, DECIMAL may be a more suitable data type despite its potential for increased storage space requirements.