MySQL总是将位值返回为空

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

从我的create table脚本中，我将hasMultipleColors字段定义为:

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)

服务器版本: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?

任何想法吗?

4 个解决方案

#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.

这是因为一个bug，参见:http://bugs.mysql.com/bug.php?id=43670。状态显示:不会修复。

#2

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

您需要执行一个转换，因为位1不能打印。

SELECT hasMultipleColors+0 from pumps where id = 1;

从id = 1的泵中选择hasMultipleColors+0;

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

在这里看到更多:http://dev.mysql.com/doc/refman/5.0/en/bit-field-literals.html

#3

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.

它将根据hasMultipleColors的值返回1或0。

#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).

有些软件可以自动处理这个问题，但是对于命令行MySQL，您必须以某种方式将它转换为int(例如，通过添加0)。

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).

在PHP这样的语言中，字符的序号值将给您正确的值，使用ord()函数(尽管要真正正确，它必须由十进制转换为二进制字符串，以便在位字段中工作时间长于一个字符)。

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/

编辑:找到一个很老的消息来源说它变了，所以MySQL升级可能会使一切工作更正常:http://gphemsley.wordpress.com/2010/02/08/php- MySQL -and- bit-field-type/

#1