MySQL: preferred column type for (commodity) prices?

Question

In MySQL, what is the preferred column type for storing product prices (or currencies in general)? Google found out that DECIMAL FLOAT is often used, but I wonder which one is better.

I store prices in the range of 0.01 to 25.00. Of course, higher values ​​may also be possible. (Note: I am not asking for the code for copy-pasta, I am just giving you more information that could help you formulate a more complete answer).

thank

Answer 1

Decimal is the one I would use

The main difference between Decimal / Numeric and Float: Float is an approximate number data type, which means that not all values ​​in a range type data can be represented exactly. Decimal / numeric - fixed precision data type, which means that all values ​​in the data type type can be accurately represented with precision and scale.

Converting from decimal or numeric to float may result in some loss of precision. For decimal or numeric data types, SQL Server treats each specific combination of precision 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, although this does not apply to float. For FLOAT (6) 11.22 and 11.2222 the same data types.

Answer 2

A decimal field is good.

If you have higher prices, you can use product_price decimal(6,2) NOT NULL, , that is, you can store prices up to 6 digits with a decimal point up to 2 digits.

The maximum value for the product_price decimal(6,2) NOT NULL, field will keep the price up to 9999.99

If all prices are between 0.01 and 25.00, then product_price decimal(4,2) NOT NULL, will be fine, but if you have higher prices, you can set any value to decimal(4,2) .

Answer 3

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

Use decimal value:

"The types DECIMAL and NUMERIC are used to store values ​​for which it is important to maintain accurate accuracy, for example, with monetary data."

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

Answer 4

I prefer INT (price multiplied by 100), which solves the floating point problem in other software.

Answer 5

The decimal is incorrect, because if you want to set the decimal price to 12.99, set it to 13.00. So this is wrong, but if you use float, you can save it as 12.99 Thus, the correct answer is float and varchar.