DOUBLE or DECIMAL in MySQL for Financial Data: When is Conversion Necessary?

Double vs. Decimal in MySQL: A Case for Optimization

While it is generally recommended to avoid using DOUBLE to store financial data in MySQL due to precision concerns, there may be exceptions where this approach is acceptable. Let's examine the arguments presented in favor of converting DOUBLE to DECIMAL.

Arguments for Conversion

1. Avoiding Precision Bugs: DOUBLE is a floating-point type prone to rounding errors, particularly when performing calculations. For example, adding 0.1 to 0.2 may result in 0.30000000000000004 instead of the expected 0.3.
2. Compliance with Recommendations: Authoritative sources, including the MySQL documentation, consistently advise against using DOUBLE for storing money values.

Arguments against Conversion

However, in this specific case, the following arguments suggest that conversion may not be necessary:

1. No Database Calculations: All financial calculations are performed in Java using BigDecimal, ensuring accuracy. MySQL is used solely for data storage and retrieval.
2. Sufficient Precision: The DOUBLE type's 15-digit precision is adequate for storing amounts with up to 2 decimal places. Occasional numbers with 8 decimal digits pose no significant risk.
3. Proven Stability: Six years of production experience with no precision-related issues support the stability of the DOUBLE type for this specific application.

The Takeaway

Based on these considerations, it is reasonable to conclude that conversion from DOUBLE to DECIMAL may not be necessary if:

• Database calculations are performed outside of MySQL.
• The required precision is within the capabilities of DOUBLE.
• Sufficient testing has demonstrated the reliability of the data stored in DOUBLE columns.

In this case, the stability and sufficient precision of DOUBLE, combined with the absence of database calculations, suggest that conversion may not provide significant benefits. However, it's important to continually monitor the system for any potential precision issues that may arise in the future.

The above is the detailed content of DOUBLE or DECIMAL in MySQL for Financial Data: When is Conversion Necessary?. For more information, please follow other related articles on the PHP Chinese website!