Should You Convert DOUBLE to DECIMAL in MySQL for Financial Data When Calculations are Performed Externally?

DOUBLE vs DECIMAL in MySQL: Re-Evaluating the Conversion Argument

It is often advised against using DOUBLE for financial data storage in MySQL due to potential precision errors. However, for an existing system with 783 DOUBLE columns, considering a conversion to DECIMAL requires careful evaluation.

In the given scenario, the system stores money values in DOUBLE columns but performs all calculations in Java using BigDecimal. MySQL is only used for result storage. Since there are no actual database calculations, the lack of precision with DOUBLE is not a concern.

Moreover, the system has operated for six years without any precision-related bugs. This suggests that for financial values up to 8 decimal digits, DOUBLE's 15-digit precision is sufficient.

Additionally, the specific database operations performed (SUM and complex multiplications) do not result in a loss of precision at the second decimal digit. This indicates that the rounding issues associated with DOUBLE are not impacting the accuracy of the stored data.

Therefore, in this specific scenario, where calculations are done outside MySQL and precision is not a significant issue, there may not be a compelling reason to convert DOUBLE columns to DECIMAL. However, it is important to note that if any SQL arithmetic operations are planned in the future, the conversion to DECIMAL should be reconsidered.

The above is the detailed content of Should You Convert DOUBLE to DECIMAL in MySQL for Financial Data When Calculations are Performed Externally?. For more information, please follow other related articles on the PHP Chinese website!