MySQL和PHP十进制精度错误 [英] MySQL & PHP decimal precision wrong

问题描述

24151.40-31891.10 = -7739.699999999997

24151.40 - 31891.10 = -7739.699999999997

我从MySQL表中获取了这两个数字，其类型为小数(14,2) 24151.40 31891.10 完全按照上面的说明进行保存，并且回显与PHP中的完全相同.但是当我从第一个值减去第二个值的那一刻，我得到一个数字-7739.699999999997而不是-7,739.7.为什么要特别精确?它是从哪里来的?

I grab these two numbers from a MySQL table with the type as decimal(14,2) 24151.40 31891.10 It is saved exactly as stated above and it echos exactly like that in PHP. But the minute I subtract the second value from the first value, I get a number -7739.699999999997 instead of -7,739.7. Why the extra precision? And where is it coming from?

推荐答案

来自一加一等于二，对吧? .2加1.4乘以10怎么样?等于16，对吧?如果您正在使用PHP(或大多数其他编程语言)进行数学运算，则不会:

One plus one equals two, right? How about .2 plus 1.4 times 10? That equals 16, right? Not if you're doing the math with PHP (or most other programming languages):

echo floor((0.2 + 1.4) * 10); // Should be 16. But it's 15!

这是由于内部如何处理浮点数.它们用固定的小数位数表示，并且可能导致数字的总和与您期望的不完全相同.在内部，我们的.2加1.4乘10示例计算得出大约为15.9999999998左右.当使用不必像百分比这样精确的数字时，这种数学很好.但是，在使用货币精度时，问题很重要，因为这里或那里丢失的一分钱或一美元很快就累加了，没有人会喜欢在任何丢失的钱中占尽头.

This is due to how floating point numbers are handled internally. They are represented with a fixed number of decimal places and can result in numbers that do not add up quite like you expect. Internally our .2 plus 1.4 times 10 example computes to roughly 15.9999999998 or so. This kind of math is fine when working with numbers that do not have to be precise like percentages. But when working with money precision matters as a penny or a dollar missing here or there adds up quickly and no one likes being on the short end of any missing money.

BC数学解决方案

幸运的是，PHP提供了 BC Math扩展精度数学PHP提供了二进制计算器，该二进制计算器支持任何大小和精度的数字，以字符串表示."换句话说，您可以使用此扩展程序对货币值进行精确的数学运算. BC Math扩展包含功能，可让您执行最常见的功能精确的操作，包括添加，除.

Fortunately PHP offers the BC Math extension which is "for arbitrary precision mathematics PHP offers the Binary Calculator which supports numbers of any size and precision, represented as strings." In other words, you can do precise math with monetary values using this extension. The BC Math extension contains functions that allow you to perform the most common operations with precision including addition, subtraction, multiplication, and division.

更好的例子

这是与上面相同的示例，但是使用bcadd()函数为我们做数学运算.它包含三个参数.前两个是我们希望添加的值，第三个是我们希望精确到的小数位数.由于我们一直在用钱，所以我们将精度设置为两位小数.

Here's the same example as above but using the bcadd() function to do the math for us. It takes three parameters. The first two are the values we wish to add and the third is the number of decimal places we wish to be precise to. Since we're working with money we'll set the precision to be two decimal palces.

echo floor(bcadd('0.2', '1.4', 2) * 10); // It's 16 like we would expect it to be.

这篇关于MySQL和PHP十进制精度错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！