Column Type for Price Field(DECIMAL or FLOAT or DOUBLE)

This has got me really interested.

In this thread someone has mentioned that FLOAT is thr wrong type of MySQL field for prices and it is better to use DECIMAL.

I have split this from that topic because it is a different matter and I have invited that member to this thread to explain his argument.

Does anyone else have any views on this as I have always been quite confident that a price is a FLOATing point number and as such should be stored in a database likewise.

Will be interesting to see what people’s views are…

MySQL 5.0 Reference Manual :: 10.2 Numeric Types

The FLOAT and DOUBLE data types are used to represent approximate numeric data values.

The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.

0.667 is an approximation of a number (2/3), $0.02 is an exact value (2 cents).

If you do arithmetic on FLOAT values, it’s possible to get into a situation where there is rounding error, and rounding errors aren’t acceptable when you’re tracking money.

Thanks for clearing that up - I for one will be using DECIMAL for price fields in future.