問(wèn)題描述

我猜這之前已經(jīng)出現(xiàn)過(guò)，但我找不到我的問(wèn)題的答案.這是一個(gè)小代碼片段:

 $stmt = $this ->分貝->詢(xún)問(wèn)("選擇`字段`從`表`在哪里(`ID` = 33608)");var_dump($stmt -> fetch());

這是我得到的結(jié)果:

 array(1) { ["Field"]=>浮動(dòng)(1.7999999523163)}

然而，MySQL數(shù)據(jù)庫(kù)中的數(shù)據(jù)是1.8.該字段的類(lèi)型是 float(7,4).$this->db 是一個(gè) PDO 對(duì)象.我最近遷移到 PDO(從 AdoDB)，并且此代碼之前運(yùn)行良好.我不確定這里出了什么問(wèn)題.你能指出我正確的方向嗎?謝謝！

如浮點(diǎn)類(lèi)型(近似值) - FLOAT、DOUBLE:

MySQL 在存儲(chǔ)值時(shí)執(zhí)行四舍五入，因此如果將 999.00009 插入到 FLOAT(7,4) 列中，近似結(jié)果為 999.0001>.

因?yàn)楦↑c(diǎn)值是近似值，而不是作為精確值存儲(chǔ)，嘗試在比較中將它們視為精確值可能會(huì)導(dǎo)致問(wèn)題.它們還受制于平臺(tái)或?qū)崿F(xiàn)依賴(lài)性.有關(guān)詳細(xì)信息，請(qǐng)參閱第 C.5.5.8 節(jié)，浮動(dòng)問(wèn)題 -點(diǎn)值"

為了獲得最大的可移植性，需要存儲(chǔ)近似數(shù)字?jǐn)?shù)據(jù)值的代碼應(yīng)使用 FLOAT 或 DOUBLE PRECISION，不指定精度或位數(shù).

因此，在將 1.8 插入您的數(shù)據(jù)庫(kù)后，MySQL 將文字四舍五入為 001.8000 并在 近似值 rel="nofollow">binary32 格式:即 0x3FE66666，其位表示:<前>簽名:0b0有偏指數(shù):0b01111111= 127(表示包括 +127 的偏差，因此 exp = 0)有效數(shù) : 0b[1.]11001100110011001100110^ 隱藏位，不以二進(jìn)制表示形式存儲(chǔ)= [1.]7999999523162841796875

這相當(dāng)于:

這是 MySQL 返回給客戶(hù)端的值.看起來(lái) AdoDB 然后檢查了列的數(shù)據(jù)類(lèi)型并相應(yīng)地對(duì)結(jié)果進(jìn)行了四舍五入，而 PDO 則沒(méi)有.

如果你想要精確的值，你應(yīng)該使用固定點(diǎn)數(shù)據(jù)類(lèi)型，例如 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.

這篇關(guān)于PHP PDO 查詢(xún)?yōu)?FLOAT 字段返回不準(zhǔn)確的值的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！