MySQL PDO错误：传递BIT列的布尔参数时，“数据太长了”

The only similar question I have found is: Insert php boolean into mysql bit column with Zend_Db but this has no answer.

我发现的唯一类似问题是：使用Zend_Db将php boolean插入mysql位列，但这没有答案。

Please see below a simplified test:

请参阅下面的简化测试：

The 'allow' column type is BIT.
The 'roleID' column type is INT.
The 'permID' column type is INT.

'allow'列类型是BIT。 'roleID'列类型是INT。 'permID'列类型是INT。

$dbo = new PDO("mysql:dbname=database;host=127.0.0.1", "phpuser", "pass");

$query = $dbo->prepare("INSERT INTO ws_role_perms (allow, roleID, permID)
                            VALUES (:allow, :roleID, :permID)");

$query->bindValue("allow", true, PDO::PARAM_BOOL);
$query->bindValue("roleID", 1, PDO::PARAM_STR);
$query->bindValue("permID", 2, PDO::PARAM_STR);

if ($query->execute() == false) {
    throw new Exception(print_r($query->errorInfo(), true));
}

The error message I receive is:

我收到的错误消息是：

Array (
    [0] => 22001
    [1] => 1406
    [2] => Data too long for column 'allow' at row 1
)

If I attempt to put the equivalent query straight into MySQL, i.e. by running the query: INSERT INTO ws_role_perms (allow, roleID, permID) VALUES (true, 1, 2) I have no problems.

如果我试图将等效查询直接放入MySQL，即通过运行查询：INSERT INTO ws_role_perms（allow，roleID，permID）VALUES（true，1,2）我没有问题。

Is this a bug in the MySQL PDO driver, or is it just me?

这是MySQL PDO驱动程序中的错误，还是仅仅是我？

Many thanks in anticipation of your help.

非常感谢您的期待。

3 个解决方案

#1

It seems as though PHP is not interpreting 'true' as a boolean. PHP.NET indicates that PDO::PARAM_BOOL is an integer. The MySQL interpretation of a column with datatype 'bit' changed after around version 5.0. What this means is that PHP is probably trying to insert a full integer (32 bits) into a column that has an unpredictable number of bits (1-64).

似乎PHP并没有将'true'解释为布尔值。 PHP.NET表示PDO :: PARAM_BOOL是一个整数。在版本5.0之后，对数据类型为“bit”的列的MySQL解释已更改。这意味着PHP可能试图将一个完整的整数（32位）插入到具有不可预测的位数（1-64）的列中。

#2

Use the Cast function:

使用Cast功能：

Values ( CAST( :allow AS UNSIGNED), :roleID, :permID)

#3

$query = $dbConnection->prepare("UPDATE roles SET isTrue= b? WHERE itemID = ?;");
$result = $query->execute(array(true,$productID_GET));

Use 'b' before the variable to make it do conversation for any value to bit.

在变量之前使用'b'使其为任何值进行对话。

In my case, I have bit column and i was getting the same error. That's how i solved it.

在我的情况下，我有位列，我得到了相同的错误。这就是我解决它的方式。

#1

#2