PDOstatement(MySQL):将值0插入bit(1)字段中会导致表中写入1 [英] PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table

问题描述

我正在使用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！