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

问题描述

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

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());

这是我得到的结果:

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

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

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!

推荐答案

如

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

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

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

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"

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

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.

因此，在将1.8插入数据库后，MySQL将文字四舍五入为001.8000，并在

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

这等于:

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

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

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.

如果需要精确值，则应使用不动点数据类型，例如DECIMAL.

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

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