MySQL: preferred column type for (product) prices?

Question

Hi all,

In MySQL, what is the preferred column type for storing a product's price (or currencies in general)? Google learned me DECIMAL of FLOAT is often used, but I wonder which one is better.

I'm storing prices ranging from 0.01 to 25.00. Of course higher values could also be possible. (Note: I'm not asking for copy-pasta code, I'm just giving you more information which could help you form a more complete answer).

Thanks

Answer 1

Decimal is the one I would use

The basic difference between Decimal/Numeric and Float : Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly. Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

Answer 2

I would not use float as that can give rounding errors, as it is a floating point type.

Use decimal:

"The DECIMAL and NUMERIC types are used to store values for which it is important to preserve exact precision, for example with monetary data."

see: http://dev.mysql.com/doc/mysql/en/numeric-types.html

Answer 3

I wrote an in-house invoicing system recently, and used INT fields containing the number of cents.

DECIMAL may be the correct choice, but if you use INT and document what you're doing, whoever maintains your code later doesn't need to know the fine details of the rarer MySQL types.