PHP PDO 查询为 FLOAT 字段返回不准确的值 [英] PHP PDO query returns inaccurate value for FLOAT fields

问题描述

我猜这之前已经出现过，但我找不到我的问题的答案.这是一个小代码片段:

 $stmt = $this ->分贝->询问("选择`字段`从`表`在哪里(`ID` = 33608)");var_dump($stmt -> fetch());

这是我得到的结果:

 array(1) { ["Field"]=>浮动(1.7999999523163)}

然而，MySQL数据库中的数据是1.8.该字段的类型是 float(7,4).$this->db 是一个 PDO 对象.我最近迁移到 PDO(从 AdoDB)，并且此代码之前运行良好.我不确定这里出了什么问题.你能指出我正确的方向吗?谢谢！

解决方案

如浮点类型(近似值) - FLOAT、DOUBLE:

<块引用>

MySQL 在存储值时执行四舍五入，因此如果将 999.00009 插入到 FLOAT(7,4) 列中，近似结果为 999.0001>.

因为浮点值是近似值，而不是作为精确值存储，尝试在比较中将它们视为精确值可能会导致问题.它们还受制于平台或实现依赖性.有关详细信息，请参阅第 C.5.5.8 节，浮动问题 -点值"

为了获得最大的可移植性，需要存储近似数字数据值的代码应使用 FLOAT 或 DOUBLE PRECISION，不指定精度或位数.

因此，在将 1.8 插入您的数据库后，MySQL 将文字四舍五入为 001.8000 并在 近似值href="http://en.wikipedia.org/wiki/Single-precision_floating-point_format" rel="nofollow">binary32 格式:即 0x3FE66666，其位表示:<前>签名:0b0有偏指数:0b01111111= 127(表示包括 +127 的偏差，因此 exp = 0)有效数 : 0b[1.]11001100110011001100110^ 隐藏位，不以二进制表示形式存储= [1.]7999999523162841796875

这相当于:

<前>(-1)^0 * 1.7999999523162841796875 * 2^0= 1.7999999523162841796875

这是 MySQL 返回给客户端的值.看起来 AdoDB 然后检查了列的数据类型并相应地对结果进行了四舍五入，而 PDO 则没有.

如果你想要精确的值，你应该使用固定点数据类型，例如 DECIMAL.

I am guessing this has came up before, but I couldn't find the answer to my question. Here is a little code snippet:

    $stmt = $this -> db -> query("
        SELECT
          `Field`
        FROM
          `Table`
        WHERE
          (`ID` = 33608)");
    var_dump($stmt -> fetch());

And this is the result I get:

    array(1) { ["Field"]=> float(1.7999999523163) }

However, the data in the MySQL database is 1.8. The type of the field is float(7,4). $this->db is a PDO object. I have recently migrated to PDO (from AdoDB), and this code was working fine before. I am not sure what went wrong here. Could you point me in the right direction? Thanks!

解决方案

As documented under Floating-Point Types (Approximate Value) - FLOAT, DOUBLE:

MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section C.5.5.8, "Problems with Floating-Point Values"

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

Therefore, upon inserting 1.8 into your database, MySQL rounded the literal to 001.8000 and encoded the closest approximation to that number in binary32 format: i.e. 0x3FE66666, whose bits signify:

Sign           : 0b0

Biased exponent: 0b01111111
               =   127 (representation includes bias of +127, therefore exp = 0)

Significand    : 0b[1.]11001100110011001100110
                    ^ hidden bit, not stored in binary representation
               =   [1.]7999999523162841796875

This equates to:

 (-1)^0 * 1.7999999523162841796875 * 2^0
=         1.7999999523162841796875

This is the value that is returned by MySQL to the client. It would appear that AdoDB then inspected the column's datatype and rounded the result accordingly, whereas PDO does not.

If you want exact values, you should use a fixed point datatype, such as DECIMAL.

这篇关于PHP PDO 查询为 FLOAT 字段返回不准确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！