Fixed Point Math in PHP with BCMath, precision loss cases

Challenges and Tips for Fixed Point Numerical Operations in PHP and MySQL

Extreme care is required when handling fixed point values, especially when developing with PHP and MySQL. This article will explore the obstacles and details encountered when using PHP BCMath extensions, MySQL fixed point expression processing, and persisting fixed point data from PHP to MySQL. Despite some challenges, we will try to figure out how to handle fixed point values ​​and avoid accuracy losses.

Summary of key points

• The BCMath extension in PHP supports arbitrary precision math operations, but can result in accuracy loss if numerical variables are passed to its functions. String values ​​representing numbers should be used instead to avoid this problem.
• MySQL supports fixed point numeric expressions, however, if any operand is in exponential or string format, it is considered a floating point number. The PHP PDO extension does not have a Decimal parameter type for binding, which may result in inaccurate results.
• To perform precise mathematical operations in a PHP MySQL application, all operations can be processed in PHP and data can only be persisted to MySQL using INSERT or UPDATE statements. Alternatively, you can manually build SQL queries to ensure that all SQL mathematical expressions are represented in decimal numbers.

BCMath's Problem

BCMath documentation states:

For arbitrary precision math operations, PHP provides a binary calculator that supports any size and precision numbers expressed as strings.

Therefore, the BCMath function parameters should be represented as strings. Passing a numeric variable to the bcmath function can result in an incorrect result, the same precision loss as the one that occurs when treating a double value as a string.

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;

The result is:

<code>77621.00
77621.100
77621.0100
5001885077621.00
5001885077621.100
5001885077621.00 //此处可见精度损失</code>

Do not pass numeric variables to the BCMath function, only string values ​​representing numbers are passed. Even if floating point numbers are not processed, BCMath will output strange results:

Case 2

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

The result is:

<code>0.0010
0 // 这真的很奇怪！！！
0.0001</code>

The reason is that BCMath converts its parameters to strings, and in some cases, the string representation of numbers has an exponential representation.

Case 3

echo bcmul('10', '1e-4', 10) . PHP_EOL; // 也输出 0

PHP is a weak-type language that, in some cases, does not have strict control over input—thinks to handle as many requests as possible.

For example, we can "fix" Case 2 and Case 3:

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

However, applying the same transformation will destroy the "correct" behavior of Case 1:

$val = sprintf("%.10f", '50018850776.2100000000');
echo bcmul('10', $val, 10) . PHP_EOL;
echo bcmul('10', 50018850776.2100000000, 10) . PHP_EOL;
500188507762.0999908450 // 错误
500188507762.10 // 正确

So the sprintf solution does not work with BCmath. Assuming all user inputs are strings, we can implement a simple validator that captures the numbers of all exponential notations and converts them correctly. This technique is implemented in php-bignumbers, so we can safely pass in parameters like 1e-20 and 50018850776.2101 without losing accuracy.

BCMath Final Guidelines

Do not use floating point numbers as fixed point operation parameters in BCMath PHP extension functions. Use only strings.

When using BCMath extension operation, pay attention to the parameters of the exponential notation. The BCMath function does not handle exponential parameters correctly (such as "1e-8"), so they should be converted manually. Be careful not to use sprintf or similar conversion techniques as this will result in a loss of accuracy.

The php-bignumbers library can be used, which can handle input parameters in exponential form and provide users with fixed-point mathematical operations. However, its performance is not as good as BCMath extensions, so it is a tradeoff between robust packages and performance.

MySQL and fixed point numbers

In MySQL, fixed point numbers are processed using the DECIMAL column type. You can read the official MySQL documentation for data types and precise math operations.

The most interesting part is how MySQL handles expressions:

The processing of numeric expressions depends on the type of value contained in the expression:

If any approximation exists, the expression is an approximation and is calculated using floating point operation.

If no approximation exists, the expression contains only exact values. If any exact value contains the fractional part (the value after the decimal point), the expression is calculated using DECIMAL precise arithmetic with a 65-digit accuracy. The word "precision" is limited by what can be represented in binary. For example, 1.0/3.0 can be approximate to .333… using decimal notation, but cannot be written as exact numbers, so (1.0/3.0)*3.0 is inaccurately calculated as 1.0.

Otherwise, the expression contains only integer values. The expression is precise and is calculated using integer arithmetic with the same precision as BIGINT (64 bits).

If a numeric expression contains any string, it is converted to a double-precision floating-point value, and the expression is an approximate value.

This is a short example that demonstrates the case of the decimal part:

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;

This looks simple, but let's see how to handle it in PHP.

Precise math operations in PHP and MySQL

So now we have to persist fixed point values ​​from PHP to MySQL. The correct way is to use preprocessing statements and placeholders in the query. Then we do parameter binding, everything is safe and reliable.

<code>77621.00
77621.100
77621.0100
5001885077621.00
5001885077621.100
5001885077621.00 //此处可见精度损失</code>

When we bind the value to the statement placeholder, we can specify its type through the third parameter of bindValue. Possible types are represented by the 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 mathematical expressions in the query are treated as floating point expressions, rather than fixed point expressions.

If we want to take advantage of preprocessing statements and use fixed point numbers, the best way is to perform all the math in PHP and save the result to MySQL.

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

Conclusion

We came to the following conclusion:

• Do not use floating point numbers as fixed point operation parameters in BCMath PHP extension functions. Use only strings.
• BCMath extension does not apply to string numbers for exponential notation.
• MySQL supports fixed point numeric expressions, but all operands must be in decimal format. If at least one parameter is in exponential or string format, it is treated as a floating point number and the expression is calculated as a floating point number.
• PHP PDO extension does not have a Decimal parameter type, so if you use a preprocessing statement and bind parameters in a SQL expression containing fixed point operands, you won't get exact results.
• To perform precise math operations in a PHP MySQL application, you can choose from two methods. The first method is to process all operations in PHP and only use INSERT or UPDATE statements to persist data to MySQL. In this case, you can use preprocessed statements and parameter bindings. The second approach is to build SQL queries manually (you can still use preprocessing statements, but you have to escape the parameters yourself) so that all SQL mathematical expressions are expressed in decimal numbers.

My personal favorite method is the first: do all the math in PHP. I agree that PHP and MySQL may not be the best choice for applications that require precise math operations, but if you choose this tech stack, it is good to know how to handle it correctly.

(The FAQs part is omitted due to space limitations. If necessary, the FAQs part can be generated separately.)

The above is the detailed content of Fixed Point Math in PHP with BCMath, precision loss cases. For more information, please follow other related articles on the PHP Chinese website!