MySQL: (产品)价格的首选列类型？

在 MySQL 中，存储产品价格(或一般货币)的首选列类型是什么？Google 告诉我 DECIMAL of FLOAT 经常被使用，但是我想知道哪一个更好。

我存储的价格从0.01到25.00不等。当然，更高的价值也是可能的。(注意: 我不是要求复制意大利面代码，我只是给你更多的信息，可以帮助你形成一个更完整的答案)。

谢谢

mysql

115243 次浏览

小开

最佳答案

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.

小开

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

小开

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).