Java和SQL Server中的精确噩梦 [英] Precision nightmare in Java and SQL Server

问题描述

我一直在努力应对Java和SQL Server中的精确噩梦，直到​​我不知道为止。就个人而言，我理解这个问题及其根本原因，但向全球客户解释这一点是不可行的（至少对我而言）。

I've been struggling with precision nightmare in Java and SQL Server up to the point when I don't know anymore. Personally, I understand the issue and the underlying reason for it, but explaining that to the client half way across the globe is something unfeasible (at least for me).

情况就是这样。我在SQL Server中有两列--Qty INT和Price FLOAT。这些值为 - 1250和10.8601 - 因此，为了获得总值，其数量*价格和结果为13575.124999999998（在Java和SQL Server中）。那是对的。问题是这个 - 客户端不想看到它，他们只看到这个数字为13575.125就是这样。在一个地方，他们可以用2位小数精度看到它，另外4位小数。当以4位小数显示时，数字是正确的 - 13575.125，但是当以2位小数显示时它们认为它是错误的 - 13575.12 - 应该是13575.13！

The situation is this. I have two columns in SQL Server - Qty INT and Price FLOAT. The values for these are - 1250 and 10.8601 - so in order to get the total value its Qty * Price and result is 13575.124999999998 (in both Java and SQL Server). That's correct. The issue is this - the client doesn't want to see that, they see that number only as 13575.125 and that's it. On one place they way to see it in 2 decimal precision and another in 4 decimals. When displaying in 4 decimals the number is correct - 13575.125, but when displaying in 2 decimals they believe it is wrong - 13575.12 - should instead be 13575.13!

帮助。

推荐答案

您的问题是您正在使用浮点数。在java方面，你需要使用BigDecimal，而不是float或double，在SQL方面你需要使用Decimal（19,4）（如果它有助于跳转到你的精度级别，则使用Decimal（19,3））。不要使用Money类型，因为SQL中的Money类型的数学会导致截断，而不是舍入。数据存储为浮点类型（你说它是不可更改的）这一事实不会影响这一点，你只需要在第一次机会之前转换它就可以进行数学运算。

Your problem is that you are using floats. On the java side, you need to use BigDecimal, not float or double, and on the SQL side you need to use Decimal(19,4) (or Decimal(19,3) if it helps jump to your precision level). Do not use the Money type because math on the Money type in SQL causes truncation, not rounding. The fact that the data is stored as a float type (which you say is unchangeable) doesn't affect this, you just have to convert it at first opportunity before doing math on it.

在给出的具体示例中，您需要首先获得4位小数精度数，并根据具体情况将其放入BigDecimal或Decimal（19,4）中，然后进一步将其舍入为2位小数精度。然后（如果你正在四舍五入）你将获得你想要的结果。

In the specific example you give, you need to first get the 4 decimal precision number and put it in a BigDecimal or Decimal(19,4) as the case may be, and then further round it to 2 decimal precision. Then (if you are rounding up) you will get the result you want.

这篇关于Java和SQL Server中的精确噩梦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！