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.

Answer 2

pdo by default doesnt use prepared statements for the mysql driver, it emulates them by creating dynamic sql behind the scenes for you. The sql sent to mysql ends up being a single quoted 0 like '0', which mysql interprets as a string, not a number.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

It should work now, and you also will be actually using real prepared statements.

Answer 3

Because prepare adds ' to your parameter, You have only to add b before parameter name

$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (?, b?)');
$statement->execute(array("TEST", 1 /* or TRUE */));

Note: you can use 1, 0 or TRUE, FALSE.