MySQL：（产品）价格的首选列类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1796334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: preferred column type for (product) prices?
提问by SolidSmile
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.
在 MySQL 中，存储产品价格（或一般货币）的首选列类型是什么？Google 了解到我经常使用 DECIMAL 的 FLOAT，但我想知道哪个更好。
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).
我存储的价格范围从 0.01 到 25.00。当然，更高的值也是可能的。（注意：我不是要求复制意大利面代码，我只是为您提供更多信息，这些信息可以帮助您形成更完整的答案）。
Thanks
谢谢
回答by Sheff
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.
Decimal/Numeric 和 Float 的基本区别：Float 是 Approximate-number 数据类型，这意味着并非数据类型范围内的所有值都可以精确表示。Decimal/Numeric 是Fixed-Precision 数据类型，这意味着reane 数据类型中的所有值都可以精确地表示为精度和小数位数。
从 Decimal 或 Numeric 转换为浮点数可能会导致一些精度损失。对于 Decimal 或 Numeric 数据类型，SQL Server 将精度和小数位数的每个特定组合视为不同的数据类型。DECIMAL(4,2) 和 DECIMAL(6,4) 是不同的数据类型。这意味着 11.22 和 11.2222 是不同的类型，尽管浮点数不是这种情况。对于 FLOAT(6) 11.22 和 11.2222 是相同的数据类型。
回答by Ali Nawaz
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.
如果您有更高的价格，那么您可以使用product_price decimal(6,2) NOT NULL,ie 您可以存储最多 6 位数字的价格，小数点在 2 位数字之前。
Maximum value for field product_price decimal(6,2) NOT NULL,will store price up to 9999.99
字段的最大值product_price decimal(6,2) NOT NULL,将存储价格高达 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).
如果所有价格都在 0.01 到 25.00 之间，那么product_price decimal(4,2) NOT NULL,会很好，但如果您有更高的价格，那么您可以在decimal(4,2).
回答by Razzie
I would not use float as that can give rounding errors, as it is a floating point type.
我不会使用 float ，因为它会产生舍入错误，因为它是一种浮点类型。
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."
“ DECIMAL 和 NUMERIC 类型用于存储保持精确精度很重要的值，例如货币数据。”
回答by Peter
I prefer INT (price multipled by 100) which solves floating point problem in other software.
我更喜欢 INT（价格乘以 100），它解决了其他软件中的浮点问题。
回答by Berk Kanburlar
Decimal is wrong because if you want to set the price 12.99 decimal turn it to 13.00.So this is wrong but if you use float you can save it as 12.99. So the correct answer is float and varchar.
Decimal 是错误的，因为如果您想将价格 12.99 设置为 13.00。所以这是错误的，但如果您使用浮点数，您可以将其保存为 12.99。所以正确答案是 float 和 varchar。
