Perl / mysql浮点不精确 [英] Perl/mysql floating point imprecision

问题描述

我正在使用Perl来与浮点数字通信。我在perl中执行了一个乘法运算：

  $ var = 0.001 * 3; 
  

然后将这个值存储在一个DOUBLE类型的列中的mysql数据库中。我以后检索结果，进一步乘法和除了数字，并将其存储回数据库中。 0.001 * 1 + 0.001 * 0.5。

存储在数据库中的结果应该是 0.0045 ： 0.0045000000000000005 。我试图了解不精确的来源在哪里。是Perl还是数据库？什么是正确的方法来处理这种浮点交互，以避免不精确？

谢谢！

10.0 times 0.1几乎没有1.0 - Brian Kernighan，编程风格的元素

这是FLOAT和DOUBLE的一个已知限制，它们是不精确的数字数据类型。这是内置于IEEE 754格式的设计中的。它会影响所有使用这种格式存储浮点数的程序设计语言。

MySQL在本附录中记录了这一点： B.5.5.8浮点值问题。

PHP在上记录这一点警告：浮点精度< a>。

如果您想在MySQL中使用缩放的数字数据类型来避免这种舍入行为，请使用 DECIMAL 。

I'm using Perl to communicate floating point numbers with a mysql database. I perform a multiplication in perl:

 $var = 0.001 * 3;

I then store this value in a mysql database in a column of type DOUBLE. I later retrieve the result, perform a further multiplication and addition to the number and store it back into the database

$previous_result_from_db += 0.001*1 + 0.001*0.5.

The result stored in the database should be 0.0045, but instead I get: 0.0045000000000000005. I'm trying to understand where the source of the imprecision is. Is it Perl or the database? What is the correct way to handle this kind of floating point interaction to avoid the imprecision?

Thanks!

解决方案

"10.0 times 0.1 is hardly ever 1.0" -- Brian Kernighan, The Elements of Programming Style

It is a known limitation of FLOAT and DOUBLE that they are imprecise numeric data types. This is built into the design of the IEEE 754 format. It affects all programming languages that store floating-point numbers using this format.

MySQL documents this in this appendix: B.5.5.8 Problems with Floating-Point Values.

PHP documents this in Warning: Floating point precision.

If you want a scaled numeric data type in MySQL that avoids this rounding behavior, use DECIMAL.

这篇关于Perl / mysql浮点不精确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！