PDOstatement (MySQL):将值 0 插入 bit(1) 字段会导致表中写入 1

问题描述

我使用 bit(1) 字段来存储布尔值并使用 PDO 准备好的语句写入表中.

I'm using a bit(1) field to store boolean values and writing into the table using PDO prepared statements.

这是测试表:

CREATE TABLE IF NOT EXISTS `test` (
  `SomeText` varchar(255) NOT NULL,
  `TestBool` bit(1) NOT NULL DEFAULT b'0'
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

这是测试代码:

$pdo = new PDO("connection string etc") ;
$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (?,?)') ;
$statement->execute(array("TEST",0)) ;

运行该代码会在 TestBool 下给我一个值为 1 的行.同样的事情使用 bindValue() 和 bindParm().我还尝试了命名占位符(而不是?)，结果相同.

Running that code gives me a row with value 1 under TestBool.And the same thing using bindValue() and bindParm(). I also tried named placeholders (instead of ?) with the same result.

然后我尝试了:

$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES ("TEST",0)') ;
$statement->execute() ;

哪个工作正常(TestBool 的值为 0).将 SQL 直接插入 MySQL 也可以.

Which worked properly (TestBool has value 0). Punching in the SQL directly into MySQL also works.

请注意，插入 1 始终有效.

Note that inserting 1 always works.

那么为什么占位符不能插入值 0?(我该怎么做?)

So why would placeholders fail to insert the value 0? (and how do I actually do it?)

推荐答案

BIT 列是 mysql 中的二进制类型(尽管它被记录为数字类型 - 这并不完全正确)并且由于客户端库的问题，我建议避免它(PDO 问题证明了这一点).如果将列类型修改为 TINYINT(1)

BIT column is a binary type in mysql (though it's documented as numeric type - that's not precisely true) and I advise to avoid it due to problems with client libraries (which PDO issue proves). You will spare yourself a lot of trouble if you modify type of column to TINYINT(1)

TINYINT(1) 当然会为每一行消耗完整字节的存储空间，但根据 mysql docs BIT(1) 也会这样做.

TINYINT(1) will of course consume full byte of storage for every row, but according to mysql docs BIT(1) will do as well.

来自:http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

位存储要求是:大约 (M+7)/8 个字节，这表明 BIT(M) 列也是字节对齐的.

bit storage requirement is: approximately (M+7)/8 bytes which suggests that BIT(M) column is also byte-aligned.

我也发现了这个:https://bugs.php.net/bug.php?id=50757

因此您可以检查以下代码是否按预期工作:

So you could check if following code works as you expect:

$pdo = new PDO("connection string etc") ;
$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (:someText,:testBool)') ;
$statement->bindValue(':someText', "TEST");
$statement->bindValue(':testBool', 0, PDO::PARAM_INT);
$statement->execute();

您也可以尝试使用与 PARAM_INT 不同的类型提示，即使您使它起作用，我仍然建议更改为 TINYINT.

You may also try with different type hints than PARAM_INT, still even if you make it work I advice to change to TINYINT.

这篇关于PDOstatement (MySQL):将值 0 插入 bit(1) 字段会导致表中写入 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！