返回的四舍五入数字为“0.999999999992345”有时
我有一份报告应该返回类似的内容。
SELECT brand, ROUND(SUM(count * price) / SUM(count), 2)
WHERE ... GROUP BY brand, ...;
问题是，我有时在我的perl代码中得到9990.32999999999992345，而不是直接SQL请求返回的9990.33。
该数字在 fetchrow_hashref 之后就开始以这种方式显示（如果有的话）。在不同的查询中，相同的数字可以以“好”或“坏”的形式出现，但在任何特定查询中总是以相同的方式出现。
我怎样才能追踪到这个？
I have a report that should return something along the lines of
SELECT brand, ROUND(SUM(count * price) / SUM(count), 2)
WHERE ... GROUP BY brand, ...;
The problem is, I sometimes get 9990.32999999999992345 in my perl code instead of 9990.33 which direct SQL request returns.
The number starts looking that way right after fetchrow_hashref, if it ever does. The same number can come in 'good' or 'bad' form in different queries, but always the same way in any specific query.
How can I track this down?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
在这里阅读有关浮点精度问题的所有信息：http://en.wikipedia.org/wiki/Floating_point#准确性_问题
Read all about floating point accuracy problems here: http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems
正如 mellamokb 所说，你必须对浮点数进行四舍五入。更重要的是，
count和price可能意味着您正在计算某物的价格。 正如本页对 FLOAT 和 DOUBLE 的解释数据类型的计算是近似的，而对于 DECIMAL 来说，计算是精确的。对于您的特定示例，出现问题的可能性很小，但如果您对价格进行大量计算，则不会出现问题。通常的规则是始终使用精确的数据类型来计算价格。As mellamokb said, you have to round your floating-point numbers. More importantly,
countandpriceprobably means that you are calculating the price of something. As this page explains for the FLOAT and DOUBLE datatype, calculations are approximate while for DECIMAL they are exact. For your particular example, the chance is low that will give problems but not if you do a lot of calculations with yourprice. The usual rule is to always use exact datatypes for calculating prices.在屏幕上显示浮点数时，始终四舍五入。并将其作为显示的最后一步进行。任何中间操作都有可能导致此类问题。
Always round floating point numbers when displaying them on the screen. And do it as the final step as it is displayed. Any intermediate operation has the potential to cause problems like this.
我可以想到造成这种情况的几个原因，但首先：
在 ROUND 周围放置
CONCAT( '', ... )有什么区别吗？你使用什么版本的 perl？ perl -V:nvtype 报告什么？I can think of a couple of causes of this, but first:
Does it make any difference to put a
CONCAT( '', ... )around your ROUND? What version of perl are you using? What does perl -V:nvtype report?33/100 是二进制周期数，就像 1/3 是十进制周期数一样。
因此，将其存储为浮点数将需要无限的存储空间。为了避免这个问题，您需要将数字存储为字符串，或者提前（在查询中，在它是浮点之前）或后期（通过四舍五入）。
33/100 is a periodic number in binary just like 1/3 is a periodic number in decimal.
Therefore, it would take infinite storage to store it as a floating point number. To avoid the problem, you'll need to store the number as a string, either early (in the query before it's a float) or late (by rounding).
这是浮点数固有的问题。这是一个设计特征，而不是缺陷。
确保从数据库返回的值不是浮点值，而是字符串或小数。 （如果`price`和`count`的数据类型都是DECIMAL，那么结果表达式应该是DECIMAL。
如果其中任何一个是浮点数，那么你可以转换为DECIMAL...
或者转换为字符串
你可以让转换为 DECIMAL 为您进行舍入 如果您将 DECIMAL 或 VARHCAR 返回给 Perl，则应该避免浮点问题
更一般地，要处理 Perl 中浮点的表示（舍入），您可以使用 sprintf 函数进行格式化。 ，例如
It's an issue inherent with floating point numbers. It's a design feature, not a flaw.
Make sure the value returned from the database is not a floating point value, but a string or decimal. (If the data types of `price` and `count` are both DECIMAL, then the resulting expression should be DECIMAL.
If either of those is a floating point, then you can convert to DECIMAL...
Or convert to a string
You can let the conversion to DECIMAL do the rounding for you. If you return a DECIMAL or VARHCAR to Perl, that should avoid floating point issues.
More generally, to handle representation (rounding) of floating point in Perl, you can format using the sprintf function, e.g.