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?

Why aren't you using BOOL instead of BIT for that? From the semantics of your field name, it'd make more sense. — Romain
– Romain, Commented Jul 23, 2012 at 9:11
Did some reading regarding the BOOL vs. BIT vs. TINYINT data-types, and the take-away I took was that MySQL handles BOOL in a very poor manner - not portable to other RDBMS solutions - so it's generally ideal to go with TINYINT or BIT (more efficient). — CdrXndr
– CdrXndr, Commented Jul 24, 2012 at 4:33

RTB · Accepted Answer · 2012-07-23 09:17:21Z

66

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.

answered Jul 23, 2012 at 9:17

RTB

5,8337 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CdrXndr Over a year ago

Got it, thanks. So this is just an issue of display rather than data being properly captured and stored. Any way to automatically cast bit datatypes when using a * in select?

Jeffrey Simon Over a year ago

I have a very weird version of this, where in the first case, the BIT fields display with no problem. But then in a different table, where the BIT fields are defined just like in the first table, nothing prints unless I do something like add 0 to each BIT column. If this is a bug, then why is it just working in the first case but not the second? TL:DR to give the gory details. One other weirdness: while this problem occurs in the mysql client on my server, the client built into my IDE (PHPStorm) does not have the problem at all, and just works with BIT fields.

A J Over a year ago

@RTB there is a simple solution to this problem and I added it.

A J · Accepted Answer · 2018-09-18 04:20:06Z

8

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.

edited Sep 18, 2018 at 4:20

answered Dec 11, 2015 at 6:23

A J

4,01214 gold badges40 silver badges55 bronze badges

Comments

A J · Accepted Answer · 2015-12-11 06:55:18Z

6

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

edited Dec 11, 2015 at 6:55

A J

4,01214 gold badges40 silver badges55 bronze badges

answered Jul 23, 2012 at 9:11

mihaisimi

2,03914 silver badges15 bronze badges

Comments

Flygenring · Accepted Answer · 2014-10-07 06:22:17Z

The actual reason for the effect you see, is that it's done right and as expected.

The bit field has bits and thus return bits, and trying to output a single bit as a character will show the character with the given bit-value – in this case a zero-width control character.

Some software may handle this automagically, but for command line MySQL you'll have to cast it as int in some way (e.g. by adding zero).

In languages like PHP the ordinal value of the character will give you the right value, using the ord() function (though to be really proper, it would have to be converted from decimal to binary string, to work for bit fields longer than one character).

EDIT:
Found a quite old source saying that it changed, so a MySQL upgrade might make everything work more as expected: http://gphemsley.wordpress.com/2010/02/08/php-mysql-and-the-bit-field-type/

Collectives™ on Stack Overflow

MySQL always returning BIT values as blank

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related