PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table

Question

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

This is the test table:

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

This is the test code:

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

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.

Then I tried:

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

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

Note that inserting 1 always works.

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

Answer 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) will of course consume full byte of storage for every row, but according to mysql docs BIT(1) will do as well.

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

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

Also I found this: 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();

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