create database binaryTypeStudy;
use binaryTypeStudy;
create table binaryTypes(bin binary(5),varbin varbinary(5) );
insert into binaryTypes values('ab','ab');
select *from binaryTypes;
insert into binaryTypes values('ab\0','ab\0');
insert into binaryTypes values(b'0110000101100010',b'0110000101100010');
select *from binaryTypes;
select *from binaryTypes where bin='ab';
select *from binaryTypes where bin='ab\0\0\0';
+--------------+----------------+
| bin | varbin |
+--------------+----------------+
| 0x6162000000 | 0x6162 |
+--------------+----------------+
1 row in set (0.00 sec)
显示的是十六进制 ab 若显示二进制 那看起来 非常怪异
而且不符合人的思维逻辑 设置为十六进制非常合理
+--------------+----------------+
| bin | varbin |
+--------------+----------------+
| 0x6162000000 | 0x6162 |
| 0x6162000000 | 0x616200 |
+--------------+----------------+
2 rows in set (0.00 sec)
与char 和varchar类似的结构 一点没差的显示 varbin第二行 补数值0
mysql> select *from binaryTypes;
+--------------+----------------+
| bin | varbin |
+--------------+----------------+
| 0x6162000000 | 0x6162 |
| 0x6162000000 | 0x616200 |
| 0x6162000000 | 0x6162 |
+--------------+----------------+
3 rows in set (0.00 sec)
以二进制串方式插入效果也是一致
mysql> select *from binaryTypes where bin='ab';
Empty set (0.00 sec)
查询失败 没有这个ab的!
要想查询成功你必须指定全 000000 ->'\0\0\0'; 变成'ab\0\0\0'
mysql> select * from binaryTypes where bin='ab\0\0\0';
+--------------+----------------+
| bin | varbin |
+--------------+----------------+
| 0x6162000000 | 0x6162 |
| 0x6162000000 | 0x616200 |
| 0x6162000000 | 0x6162 |
+--------------+----------------+
若想查询bin的数据不想显示varbin的话
mysql> select bin from binaryTypes where bin='ab\0\0\0';
+--------------+
| bin |
+--------------+
| 0x6162000000 |
| 0x6162000000 |
| 0x6162000000 |
+--------------+
3 rows in set (0.00 sec)