Home >Database >Mysql Tutorial >Should I Stick With DOUBLE for Money Storage in MySQL Even Though Experts Recommend DECIMAL?

Should I Stick With DOUBLE for Money Storage in MySQL Even Though Experts Recommend DECIMAL?

Susan Sarandon
Susan SarandonOriginal
2024-11-18 07:52:02233browse

Should I Stick With DOUBLE for Money Storage in MySQL Even Though Experts Recommend DECIMAL?

DOUBLE vs. DECIMAL for Money Storage in MySQL

Despite numerous warnings against using DOUBLE for monetary values, a developer raised concerns about optimizing an existing system with 783 DOUBLE columns. While recognizing the advantages of DECIMAL, the developer presented three reasons justifying the continued use of DOUBLE:

  1. No database calculations are performed; all operations are handled in Java using BigDecimal.
  2. DOUBLE's 15-digit precision is sufficient for storing amounts with 2 decimal digits and small numbers with 8 decimal digits.
  3. Six years of production data show no precision-related issues.

However, experts point out that DOUBLE can still introduce rounding errors, as demonstrated by running a query like SELECT columnName * 1.000000000000000 FROM tableName. They emphasize the importance of avoiding floating-point calculations when dealing with financial data, regardless of whether SQL arithmetic operations are being used.

Even if the impact of rounding errors may seem negligible, experts recommend using DECIMAL for storing monetary values. The precision and reliability of DECIMAL provide a higher level of confidence in the accuracy of financial data.

The above is the detailed content of Should I Stick With DOUBLE for Money Storage in MySQL Even Though Experts Recommend DECIMAL?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn