该字段不是空白.它的二进制值为0.
您没有将默认值定义为0,即具有ASCII代码48的字符.您将默认值定义为二进制值0,即ASCII中的空字符.当您尝试将ASCII nul打印为字符串时,没有可见的表示形式.同样,值1是二进制值1或Control-A,它也不是打印字符.
mysql> create table t (isTrial bit(1) not null default 0);
mysql> insert into t () values ();
mysql> insert into t (isTrial) values (DEFAULT);
mysql> insert into t (isTrial) values (0);
mysql> insert into t (isTrial) values (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---------+
| isTrial |
+---------+
| |
| |
| |
| |
+---------+
mysql> pager cat -v
PAGER set to 'cat -v'
mysql> select * from t;
+---------+
| isTrial |
+---------+
| |
| |
| |
| ^A |
+---------+
但PHP完成了大多数用户可能想要的,即将这些BIT值分别映射到字符串值“0”和“1”.这是一些测试代码(使用PHP 5.3.15,MySQL 5.5.30):
$stmt = $dbh->prepare("select isTrial from t");
$result = $stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
var_dump($row);
if(!empty($row['isTrial']) && (bool)$row['isTrial'] == TRUE )
{
echo "isTrial is true\n";
} else {
echo "isTrial is false\n";
}
echo "\n";
}
输出:
$php bit.php
array(1) {
["isTrial"]=>
string(1) "0"
}
isTrial is false
array(1) {
["isTrial"]=>
string(1) "0"
}
isTrial is false
array(1) {
["isTrial"]=>
string(1) "0"
}
isTrial is false
array(1) {
["isTrial"]=>
string(1) "1"
}
isTrial is true
你的评论:
使用BIT(1)应该适用于PHP,但是当我们直接在查询工具中查看数据时会引起混淆.如果要使数据更清晰,可以使用TINYINT.另一个选项是CHAR(1)CHARACTER SET ASCII.两者都需要1个字节用于存储.
BIT(1)的唯一优点是它拒绝除0或1之外的任何值.但是这种数据类型的存储要求最终会舍入到1个字节.