我的一个朋友说,他们在使用小数列存储货币格式方面遇到了一些奇怪的事情,例如保存1000.00会导致将其存储为999.99.

但是,我刚刚测试过,将1000.00作为1000.0000存储在十进制(19,4)/ MySQL 5上.谁能提供他们为什么会遇到问题的见解？也许这是一个古老的MySQL错误,在将其保存到数据库之前在应用程序端进行了不正确的计算？

这是针对ROI字段的,我存储的货币值可能高达数千美元,仅供参考.

解决方法:

您应该将DECIMAL数据类型用于精确的数字表示,而不是DOUBLE.从MySQL Documentation on Numeric Types报价：

MySQL supports all of the standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

…

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.5.8, 07001.