双精度浮点转定点_具有BCMathPHP中的定点数学，精度损失情况

最新推荐文章于 2024-04-23 13:01:43 发布

culh2177

最新推荐文章于 2024-04-23 13:01:43 发布

阅读量601

点赞数

文章标签：字符串 java python mysql 机器学习

原文链接：https://www.sitepoint.com/fixed-point-math-php-bcmath-precision-loss-cases/

版权

双精度浮点转定点

When dealing with fixed point numbers, you have to be very careful – especially if you develop with PHP and MySQL. In this article, obstacles and subtleties of working with the PHP BCMath extension, MySQL fixed point expression handling and persisting fixed point data from PHP to MySQL are described. Despite the occurring barriers we try to figure out how to work with fixed point numbers and not to lose a digit.

处理定点数时，必须非常小心-特别是如果您使用PHP和MySQL开发。在本文中，描述了使用PHP BCMath扩展，MySQL定点表达式处理以及将定点数据从PHP保留到MySQL的障碍和精妙之处。尽管存在障碍，我们仍尝试找出如何使用定点数而不丢失数字。

BCMath的麻烦 (Troubles with BCMath)

BCMath documentation says:

BCMath文档说：

For arbitrary precision mathematics PHP offers the Binary Calculator which supports numbers of any size and precision, represented as strings.

对于任意精度数学，PHP提供了二进制计算器，该计算器支持任意大小和精度的数字，以string表示。

So BCMath function parameters should be represented as strings. Passing numeric values to bcmath can lead to wrong results, the same precision loss as when we treat double value as string

因此，BCMath函数参数应表示为字符串。将数值传递给bcmath可能会导致错误的结果，与我们将double值视为字符串时的精度损失相同

情况1 (Case 1)

echo bcmul(776.210000, '100', 10) . PHP_EOL;
    echo bcmul(776.211000, '100', 10) . PHP_EOL;
    echo bcmul(776.210100, '100', 10) . PHP_EOL;

    echo bcmul(50018850776.210000, '100', 10) . PHP_EOL;
    echo bcmul(50018850776.211000, '100', 10) . PHP_EOL;
    echo bcmul(50018850776.210100, '100', 10) . PHP_EOL;

Results are:

结果是：

77621.00
    77621.100
    77621.0100
    5001885077621.00
    5001885077621.100
    5001885077621.00 //here we can see precision loss

Never pass numeric values to BCMath functions, only string values that represent numbers. Even when not dealing with floating points, BCMath can output strange results:

切勿将数字值传递给BCMath函数，仅将代表数字的字符串值传递给BCMath函数 。即使不处理浮点，BCMath也会输出奇怪的结果：

情况二 (Case 2)

echo bcmul('10', 0.0001, 10) . PHP_EOL;
	echo bcmul('10', 0.00001, 10) . PHP_EOL;
	echo 10*0.00001 . PHP_EOL;

Results are:

结果是：

0.0010
	0 // thats really strange!!!
	0.0001

The reason for this is that BCMath converts its arguments to strings, and there are cases in which a number’s string representation has exponential notation.

原因是BCMath将其参数转换为字符串，并且在某些情况下，数字的字符串表示形式具有指数表示法。

情况3 (Case 3)

echo bcmul('10', '1e-4', 10) . PHP_EOL; //outputs 0 as well

PHP is a weakly typed language and in some cases you can’t control input in a strict way – you want to process as many requests as possible.

PHP是一种弱类型的语言，在某些情况下，您不能严格地控制输入-您希望处理尽可能多的请求。

For example we can “fix” Case 2 and Case 3 by applying sprintf transformation:

例如，我们可以通过应用sprintf转换来“修复” 案例2和案例3 ：

$val = sprintf("%.10f", '1e-5');
	echo bcmul('10', $val, 10) . PHP_EOL;
	// gives us 0.0001000000

but applying the same transformation can break Case 1 “proper” behaviour:

但是应用相同的转换可能会破坏案例1的 “适当”行为：

$val = sprintf("%.10f", '50018850776.2100000000');
	echo bcmul('10', $val, 10) . PHP_EOL;
	echo bcmul('10', 50018850776.2100000000, 10) . PHP_EOL;
	500188507762.0999908450 //WRONG
	500188507762.10 //RIGHT

So the sprintf solution is not suitable for BCmath. Assuming all user inputs are strings, we can implement a simple validator, catching all exponential notation numbers and converting them properly. This technique is done in php-bignumbers, so we can safely pass in arguments like 1e-20 and 50018850776.2101 without losing precision.

因此， sprintf解决方案不适用于BCmath。假设所有用户输入都是字符串，我们可以实现一个简单的验证器，捕获所有指数符号数并将其正确转换。这项技术是在php-bignumbers中完成的，因此我们可以安全地传递1e-20和50018850776.2101类的参数，而不会损失精度。

echo bcmul("50018850776.2101", '100', 10) . PHP_EOL;
	echo bcmul(Decimal::create("50018850776.2101"), '100', 10) . PHP_EOL;

	echo bcmul(Decimal::create("1e-8"), '100', 10) . PHP_EOL;
	echo bcmul("1e-8", '100', 10) . PHP_EOL;

	echo bcmul(50018850776.2101, '100', 10) . PHP_EOL;
	echo bcmul(Decimal::create(50018850776.2101), '100', 10) . PHP_EOL;
	
	// Result
	// 5001885077621.0100
	// 5001885077621.0100
	// 0.00000100
	// 0
	// 5001885077621.00
	// 5001885077621.00982700

But the last two lines of the example show us that floating point caveats cannot be avoided by input parsing (which is completely logical – we can not deal with PHP internal double representation).

但是该示例的最后两行向我们展示了输入解析无法避免浮点警告(这是完全合乎逻辑的，我们无法处理PHP内部双重表示形式)。

BCMath最终指南 (BCMath final guidelines)

Never use floating point numbers as fixed point operation arguments. String conversion does not help, because we can not manage the precision loss in any way.

切勿将浮点数用作定点运算参数。字符串转换无济于事，因为我们无法以任何方式管理精度损失。

When using BCMath extension operations, be careful with arguments in exponential representation. BCMath functions do not process exponential arguments (i.e. ‘1e-8’) correctly, so you should convert them manually. Be careful, do not use sprintf or similar conversion techniques, because it leads to precision loss.

使用BCMath扩展操作时，请小心使用指数表示形式的参数。 BCMath函数不能正确处理指数参数(即“ 1e-8”)，因此您应该手动转换它们。请注意，请勿使用sprintf或类似的转换技术，因为这会导致精度损失。

You can use the php-bignumbers library which handles input arguments in exponential form and provides users with fixed point math operations functions. However, its performance is worse than that of the BCMath extension, so it’s a kind of compromise between a robust package and performance.

您可以使用php-bignumbers库，该库以指数形式处理输入参数，并为用户提供定点数学运算功能。但是，它的性能比BCMath扩展的性能差，因此它是健壮的程序包和性能之间的一种折衷。

MySQL和定点数 (MySQL and fixed point numbers)

In MySQL, fixed point numbers are handled with the DECIMAL column type. You can read the official MySQL documentation for data types and precision math operations.

在MySQL中，定点数使用DECIMAL列类型处理。您可以阅读MySQL官方文档，了解数据类型和精确数学运算。

The most interesting part is how MySQL handles expressions:

最有趣的部分是MySQL如何处理表达式：

Handling of a numeric expression depends on the kind of values the expression contains:

数值表达式的处理取决于表达式包含的值的类型：

If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic.

如果存在任何近似值，则该表达式为近似值，并使用浮点算法对其求值。

If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits. The term “exact” is subject to the limits of what can be represented in binary. For example, 1.0/3.0 can be approximated in decimal notation as .333…, but not written as an exact number, so (1.0/3.0)*3.0 does not evaluate to exactly 1.0.

如果没有近似值，则表达式仅包含精确值。如果任何精确值包含小数部分(小数点后的值)，则使用DECIMAL精确算术对表达式求值，并且精度为65位数。术语“精确”受可以用二进制表示的限制。例如，可以用十进制表示法将1.0 / 3.0近似为.333…，但不能写为确切数字，因此(1.0 / 3.0)* 3.0的取值不能精确为1.0。

Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits).

否则，表达式仅包含整数值。该表达式是精确的，并且使用整数算术求值，并且精度与BIGINT(64位)相同。

If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate.

如果数字表达式包含任何字符串，则将它们转换为双精度浮点值，并且该表达式为近似值。

Here is a short example that demonstrates fractional part cases:

这是一个简短的示例，展示了部分案例：

mysql> CREATE TABLE fixed_point (
        ->   amount NUMERIC(40,20) NOT NULL
        -> ) engine=InnoDB, charset=utf8;
    Query OK, 0 rows affected (0.02 sec)

    mysql> INSERT INTO fixed_point (amount) VALUES(0.2);
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT amount, amount + 0.1, amount + 1e-1, amount + '0.1' FROM fixed_point;
    +------------------------+------------------------+---------------------+---------------------+
    | amount                 | amount + 0.1           | amount + 1e-1       | amount + '0.1'      |
    +------------------------+------------------------+---------------------+---------------------+
    | 0.20000000000000000000 | 0.30000000000000000000 | 0.30000000000000004 | 0.30000000000000004 |
    +------------------------+------------------------+---------------------+---------------------+
    1 row in set (0.00 sec)

It may seen quite straightforward, but let’s look at how to deal with it within PHP.

它看起来很简单，但是让我们看一下如何在PHP中处理它。

PHP和MySQL中的精确数学 (Precision math in PHP & MySQL)

So now we have to persist our fixed point values from PHP into MySQL. The right way is to use prepared statements and placeholders within our queries. Then we do parameter binding and everything is safe and secure.

因此，现在我们必须将定点值从PHP保留到MySQL中。正确的方法是在查询中使用准备好的语句和占位符。然后我们进行参数绑定，一切都是安全的。

$amount_to_add = "0.01";
	$stmt = $dbh->prepare("UPDATE fixed_point SET amount = amount + :amount");
	$stmt->bindValue("amount", $amount_to_add);
	$stmt->execute();

When we bind a value to a statement placeholder, we can specify its type by the bindValue third argument. Possible types are represented by constants PDO::PARAM_BOOL, PDO::PARAM_NULL, PDO::PARAM_INT, PDO::PARAM_STR, PDO::PARAM_LOB and PDO::PARAM_STMT. So the problem is that the PHP PDO extension does not have a decimal parameter type for binding. As a result, all math expressions in queries are treated as floating point expressions, not as fixed point expressions.

当我们将值绑定到语句占位符时，可以通过bindValue第三个参数指定其类型。可能的类型由常量PDO::PARAM_BOOL ， PDO::PARAM_NULL ， PDO::PARAM_INT ， PDO::PARAM_STR ， PDO::PARAM_LOB和PDO::PARAM_STMT 。因此，问题在于PHP PDO扩展名没有用于绑定的十进制参数类型。结果，查询中的所有数学表达式都被视为浮点表达式，而不是定点表达式。

$dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "
        CREATE TABLE IF NOT EXISTS fixed_point (
            amount DECIMAL(43,20)
        )
    ";
    $dbh->query($sql);

    $dbh->query("DELETE FROM fixed_point");
    $dbh->query("INSERT INTO fixed_point VALUES(0.2)");

    $amount_to_add = "0.1";
    $stmt = $dbh->prepare("UPDATE fixed_point SET amount = amount + :amount");
    $stmt->bindValue("amount", $amount_to_add);
    $stmt->execute();

    $stmt = $dbh->prepare("SELECT amount FROM fixed_point");
    $stmt->execute();
    var_dump($stmt->fetchColumn());

    //output is string(22) "0.30000000000000004000"

If we want to take the advantage of prepared statements and work with fixed point numbers, the best way is to perform all math operations in PHP and save results to MySQL.

如果我们想利用准备好的语句的优势并使用定点数，最好的方法是在PHP中执行所有数学运算并将结果保存到MySQL。

$amount_to_add = "0.1";
	$stmt = $dbh->prepare("SELECT amount FROM fixed_point");
	$stmt->execute();
	$amount = $stmt->fetchColumn();
	$new_amount = bcadd($amount, $amount_to_add, 20);
	$stmt = $dbh->prepare("UPDATE fixed_point SET amount=:amount");
	$stmt->bindValue("amount", $new_amount);
	$stmt->execute();
	$stmt = $dbh->prepare("SELECT amount FROM fixed_point");
	$stmt->execute();
	$amount_after_change = $stmt->fetchColumn();
	echo $amount_after_change . PHP_EOL;

结论 (Conclusion)

We’ve reached the following conclusions:

我们得出以下结论：

Never use floating point numbers as fixed point operations arguments in BCMath PHP extension funcitons. Only strings.
切勿在BCMath PHP扩展功能中将浮点数用作定点运算参数。仅字符串。
BCMath extension does not work with string numbers in exponential representation
BCMath扩展不适用于指数表示形式的字符串
MySQL supports fixed point number expressions, but all operands have to be in decimal format. If at least one agrument is in exponential format or string, it is treated as floating point number and the expression is evaluated as floating point number.
MySQL支持定点数表达式，但是所有操作数必须为十进制格式。如果至少一个agru的形式为指数格式或字符串，则将其视为浮点数，并将表达式评估为浮点数。
PHP PDO extension does not have Decimal parameter type, so if you use prepared statements and binding parameters in SQL expressions that contain fixed point operands – you won’t get precise results.
PHP PDO扩展名没有Decimal参数类型，因此，如果在包含定点操作数SQL表达式中使用准备好的语句和绑定参数，则不会得到精确的结果。
To perform precise math operations in PHP+MySQL applications you can choose two ways. The first one is to process all operations in PHP and persist data to MySQL only with INSERT or UPDATE statements. In this case you can use prepared statements and parameter binding. The second one is to build SQL queries manually (you can still use prepared statements, but you have to escape parameters by yourself) so all SQL math expressions are in decimal number representation.
要在PHP + MySQL应用程序中执行精确的数学运算，可以选择两种方法。第一个是处理PHP中的所有操作，并且仅使用INSERT或UPDATE语句将数据持久保存到MySQL。在这种情况下，您可以使用准备好的语句和参数绑定。第二个方法是手动构建SQL查询(您仍然可以使用准备好的语句，但必须自己转义参数)，以便所有SQL数学表达式均以十进制数表示。

My personal favorite approach is the first one: all math operations in PHP. I agree that PHP and MySQL may be not the best choice for applications with precision math, but if you chose this technology stack, it’s good to know that there is a way to deal with it the right way.

我个人最喜欢的方法是第一个：PHP中的所有数学运算。我同意PHP和MySQL可能不是精确数学应用程序的最佳选择，但是如果您选择了此技术堆栈，那么很高兴知道有一种方法可以正确处理它。

翻译自: https://www.sitepoint.com/fixed-point-math-php-bcmath-precision-loss-cases/

双精度浮点转定点

culh2177

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
双精度浮点转定点_具有BCMathPHP中的定点数学，精度损失情况

双精度浮点转定点When dealing with fixed point numbers, you have to be very careful – especially if you develop with PHP and MySQL. In this article, obstacles and subtleties of working with the PHP BCMath exte...
复制链接

扫一扫