mysql always as_MySQL always returning BIT values as blank

问题

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?

回答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.

回答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.

回答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

回答4:

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/

来源：https://stackoverflow.com/questions/11609474/mysql-always-returning-bit-values-as-blank