bigint通过PDO被截断了吗? [英] bigint truncated via PDO?

问题描述

我遇到了通过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"}

两种可能的解决方案:

1. (首选)安装php-mysqlnd，无论如何您都必须这样做，因为mysqlnd是旧libmysql连接器的新替代品.
2. 打开仿真模式-由PDO构造的查询也可以正常工作.

1. (Preferred) Install php-mysqlnd, which you have to do anyway, because mysqlnd is an new replacement for the old libmysql connector.
2. Turn emulation mode ON - a query that is constructed by PDO is also works flawless.

这篇关于bigint通过PDO被截断了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！