Are there any general gotchas using decimal rather than double in MySQL?

Question

A friend of mine said that they were experiencing oddities using a column decimalto store monetary formats, such as saving 1000.00, which would lead to saving it as 999.99.

However, I just tested and 1000.00stored both 1000.0000on decimal(19,4) / MySQL 5. Can someone make it clear why they might have problems? Perhaps this was an old MySQL error, an incorrect calculation on the application side, before storing it in the database?

This is for the field ROI, and I store monetary values, which can be in thousands, FYI.

Answer 1

You should use the data type DECIMALfor exact numeric representations, not DOUBLE. Quoting from MySQL Documentation on numeric types :

MySQL supports all standard SQL numeric data types. These types include the exact numeric data types ( INTEGER, SMALLINT, DECIMALand NUMERIC), and the data types approximate numeric FLOAT , REALand DOUBLE PRECISION).

...

, . . . B.5.5.8, .

Answer 2

1.14.2. DECIMAL Data Type Changes, , :

MySQL 254 . , , , .

, . 1000.00, .

decimal , , double.