MySQL select b'1' returns integer 1 or ASCII character 1 depending on environment

Question

On my home computer,

mysql_fetch_row( mysql_query(" select b'1' ") )[0]

returns string "1".

But when hosted on webserver it returns string having ASCII character 1.

Doc does say-

Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN().

But on my local machine it still returns "1" without any conversion done by me.

How can I have the same behavior on my web server?

If I get the same behavior then I don't have to convert my PHP codes from like

$row = mysql_fetch_row( mysql_query(" select bit1_field from .. where .. ") );
if( $row[0] === '1' ) ...;

to

... select bit1_field+0 as bit1_field ...

where bit1_field is of type bit(1).

Answer 1

It seems you are using two different drivers on the machines. There are two, php5-mysqlnd and php5-mysql. Website Factor wrote about the different behavior for BIT field in late April and I have also several machines with same version, but different drives. I's probably because the driver is not changed when upgrading from an earlier version, but when php >5.4 is installed, it gets installed with php5-mysqlnd by default. Here is the MySQL page about the differences.