MySQL BIT 使用小结

背景

在平时使用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表(来自百度百科):
ascii

参考资料:【MySQL】bit 类型引发的故事

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注