MySQL: preferred column type for (product) prices?
MysqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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(4,2) and DECIMAL(6,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.
Solution 2 - Mysql
Field type "Decimal" is good.
If you have highe prices then you can use product_price decimal(6,2) NOT NULL, i.e. you can store prices up to 6 digits with decimal point before 2 digits.
Maximum value for field product_price decimal(6,2) NOT NULL, will store price up to 9999.99
If all prices are between 0.01 to 25.00 then product_price decimal(4,2) NOT NULL, will be good, but if you will have higher prices then you can set any values in decimal(4,2).
Solution 3 - Mysql
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."