我遇到了通过PDO在MySQL的BIGINT列中存储大整数的问题
I came across a problem with storing a large integer in a BIGINT column on MySQL via PDO
如果我运行此测试:
$number = "30123456789"; var_dump($number); //prints string(11) "30123456789" $new_number = (int)$number; var_dump($new_number); //prints int(30123456789)到目前为止一切都很好...
So far so good...
如果我直接在MySQL中运行此SQL:
If I run this SQL directly in MySQL:
update my_table set bigint_field = 30123456789 where id_field = 1一切正常...
当我尝试通过PDO保存该数字并将问题简化为以下代码行时,就会出现问题:
The problem arise when I try to save that number via PDO and I reduced the problem to this line of code:
//parameterized query //update my_table set bigint_field = :bigint_field where id_field = :id_field $statement->bindValue(":bigint_field", $new_number, PDO::PARAM_INT);如果不存在可选的第三个type参数或等于PDO::PARAM_STR,则该值将被保存,否则,该值将被截断为58685709.如果我尝试保存20288976024,该值将被截断为0.在这里发生
If the optional third type parameter is absent or equals PDO::PARAM_STR then the value is saved jut fine, if not the value is truncated to 58685709. If I try to save 20288976024, the value is truncated to 0. What is happening here
我正在Debian Wheezy x64上运行PHP 5.5.33和MySQL 5.6.25
I'm running PHP 5.5.33 and MySQL 5.6.25 on Debian Wheezy x64
推荐答案我无法复制您的案子.
在x86系统上,intval()已经产生2147483647 在64位系统上,一切正常.
On a x86 system intval() already makes 2147483647 On a 64 bit system everything works fine.
将bigint值绑定为字符串可能会导致错误的结果,因为值将被强制转换为浮点数并且会失去精度.
Binding bigint values as strings may lead to wrong results, as value will be cast to a float and lose precision.
事实证明这是一个旧的libmysql问题.以这种方式配置PHP后,我能够重现该问题:
it turned out to be an old libmysql issue. Having PHP configured this way I was able to reproduce the problem:
$number = 30123456789; $new_number = 20288976024; var_dump($number, $new_number); $pdo->query("CREATE TEMPORARY TABLE bint_test(i BIGINT unsigned)"); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); $stmt = $pdo->prepare("insert into bint_test values (?)"); $stmt->bindValue(1, $number, PDO::PARAM_INT); $stmt->execute(); $stmt->bindValue(1, $new_number, PDO::PARAM_INT); $stmt->execute(); echo json_encode($pdo->query("SELECT * FROM bint_test")->fetchAll());打印出
int(30123456789) int(20288976024) [{"i":"58685717"},{"i":"0"}两种可能的解决方案:
更多推荐
bigint通过PDO被截断了吗?
发布评论