背景
在平时使用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 类型引发的故事