背景
在平时使用BIT类型的时候，总有各种问题，因此做下本实验，做个总结，以免以后再次犯错。
实验
1、未使用索引
master [localhost] {msandbox} (test) > create table bittest( b bit(8));
Query OK, 0 rows affected (0.04 sec)
master [localhost] {msandbox} (test) > insert into bittest values (b'110000');
Query OK, 1 row affected (0.01 sec)
master [localhost] {msandbox} (test) > insert into bittest values (b'1000000');
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest ;
+---------+------+------+
| bin(b) | b+0 | b |
+---------+------+------+
| 110000 | 48 | 0 |
| 1000000 | 64 | @ |
+---------+------+------+
2 rows in set (0.00 sec)
这里的b’110000′ 表示使用二进制的模式插入110000，转化为十进制为48，48的ASCII码为数字0，b’1000000’转换为十进制为64，对应的ASCII码为@；
BIN(x) 返回x的二进制编码;
我们发现bit的类型查询的时候，出来的值为二进制转化为十进制对应的ASCII码的字符串。
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='48';
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=48;
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='110000';
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=110000;
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=b'110000';
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='0';
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=0;
Empty set (0.00 sec)
在数据检索的时候，我们发现利用十进制的数字和字符串的48都可以找到对应的记录，使用数字和字符串的110000都无法找到记录，因为十进制的110000转化为二进制是没有对应的记录的。使用指定二进制格式的b’110000’是能够找到对应的记录的，利用对应的ASCII字符串无法找到记录。
我们得出结论：在未使用索引的时候，MySQL在检索bit的值是不管是数值还是字符，MySQL会对where条件进行类型转化,将字符转换为数值，然后和对应的十进制进行比较。
2、使用索引
master [localhost] {msandbox} (test) > create index idx_b on bittest(b);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='48';
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=48;
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='110000';
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=110000;
Empty set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=b'110000';
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='0';
+--------+------+------+
| bin(b) | b+0 | b |
+--------+------+------+
| 110000 | 48 | 0 |
+--------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=0;
Empty set (0.00 sec)
通过添加索引，我们发现，bit位在索引中存储的格式是bin类型，不会对where条件中的值进行转换，因此ASCII对应的数字是48和字符串为‘0’存在满足条件的记录,同样的指定二进制也是满足条件的。我们来验证下另外一条记录。
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b='@';
+---------+------+------+
| bin(b) | b+0 | b |
+---------+------+------+
| 1000000 | 64 | @ |
+---------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=64;
+---------+------+------+
| bin(b) | b+0 | b |
+---------+------+------+
| 1000000 | 64 | @ |
+---------+------+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select bin(b),b+0,b from bittest where b=b'1000000';
+---------+------+------+
| bin(b) | b+0 | b |
+---------+------+------+
| 1000000 | 64 | @ |
+---------+------+------+
1 row in set (0.00 sec)
结果和预期一致。
小结
因此在使用BIT的过程中，不管有没有索引，我们在where条件中使用数值类型（二进制对应的十进制），避免使用字符串，或者指定二进制，来避免出现因为添加索引导致的结果错误。
附ASCII表（来自百度百科）：
参考资料：【MySQL】bit 类型引发的故事
近期评论