MySQL always returning BIT values as blank

Question

From my create table script, I've defined the hasMultipleColors field as a BIT:

hasMultipleColors BIT NOT NULL,

When running an INSERT, there are no warnings thrown for this or the other BIT fields, but selecting the rows shows that all BIT values are blank.

Manually trying to UPDATE these records from the command line gives odd effect - shows that the record was match and changed (if appropriate), but still always shows blank.

Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

mysql> update pumps set hasMultipleColors = 1 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select hasMultipleColors from pumps where id = 1;
+-------------------+
| hasMultipleColors |
+-------------------+
|                  |
+-------------------+
1 row in set (0.00 sec)

mysql> update pumps set hasMultipleColors = b'0' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select hasMultipleColors from pumps where id = 1;
+-------------------+
| hasMultipleColors |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

Any thoughts?

Answer 1

You need to cast the bit field to an integer.

mysql> select hasMultipleColors+0 from pumps where id = 1;

This is because of a bug, see: http://bugs.mysql.com/bug.php?id=43670. The status says: Won't fix.

Answer 2

You can cast BIT field to unsigned.

  SELECT CAST(hasMultipleColors AS UNSIGNED) AS hasMultipleColors 
  FROM pumps 
  WHERE id = 1

It will return 1 or 0 based on the value of hasMultipleColors.

Answer 3

You need to perform a conversion as bit 1 is not printable.

SELECT hasMultipleColors+0 from pumps where id = 1;

See more here: http://dev.mysql.com/doc/refman/5.0/en/bit-field-literals.html