Are there any common gotchas with using decimal as opposed to double in MySQL?

Question

A friend of mine said that they experienced oddities with using the decimal column to store money formats such as saving 1000.00 which would result in it being stored as 999.99.

However I just tested and 1000.00 is stored as 1000.0000 on decimal(19,4) / MySQL 5. Can anyone offer insight into why they may have experienced issues? Perhaps it was an old MySQL bug, improper calculation on the application side before saving it to the database?

This is for an ROI field and I'm storing money values which can be up in the thousands, FYI.

Answer 1

You should use the DECIMAL data type for exact numeric representations, and not DOUBLE. Quoting from the MySQL Documentation on Numeric Types:

MySQL supports all of the standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

...

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.5.8, Problems with Floating-Point Values.

Answer 2

On 1.14.2. DECIMAL Data Type Changes where changes to the decimal type are discussed it says this:

In older versions of MySQL, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.

This could be the source of the error your friend talked about. If the value of 1000.00 was calculated it would be prone to floating point errors.

I would choose decimal for storing monetary values as the calculations will be more accurate than using double.