PHP PDO 查询为 FLOAT 字段返回不准确的值
问题描述
我猜这之前已经出现过,但我找不到我的问题的答案.这是一个小代码片段:
这是 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:
And this is the result I get:
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 aFLOAT(7,4)
column, the approximate result is999.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
orDOUBLE 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 字段返回不准确的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!