关于MySQL的BIT类型

深藏阁楼爱情的钟 2022-02-19 09:25 242阅读 0赞

BIT类型，用于存放位字段类型，BIT(M)可以存放多位二进制数，M的范围是1-64，默认是1，如：

mysql> create table test(id int primary key,col1 bit);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> desc test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   | PRI | NULL    |       |
    | col1  | bit(1)  | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

直接使用select是无法看到bit类型的内容的，

mysql> insert into test values (1,1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |     |
    +----+------+
    1 row in set (0.00 sec)

可以用bin()或hex()函数进行读取，

mysql> select bin(col1),hex(col1) from test;
    +-----------+-----------+
    | bin(col1) | hex(col1) |
    +-----------+-----------+
    | 1         | 1         |
    +-----------+-----------+
    1 row in set (0.00 sec)

BIT类型可以进行数值比较，

mysql> select bin(col1) from test where col1=1;
    +-----------+
    | bin(col1) |
    +-----------+
    | 1         |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> select bin(col1) from test where col1<10;
    +-----------+
    | bin(col1) |
    +-----------+
    | 1         |
    +-----------+
    1 row in set (0.00 sec)

数据插入bit类型字段时，先转换为二进制，如果bit类型字段的位数允许，插入成功，否则插入失败，

mysql> insert into test values (2,2);
    ERROR 1406 (22001): Data too long for column 'col1' at row 1

当向bit(1)的col1列插入2时，由于2的二进制表示为10，需要两位，所以插入失败。将col1改为bit(2)即可插入成功：

mysql> alter table test modify col1 bit(2);
    Query OK, 1 row affected (0.05 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> insert into test values (2,2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select id,bin(col1) from test;
    +----+-----------+
    | id | bin(col1) |
    +----+-----------+
    |  1 | 1         |
    |  2 | 10        |
    +----+-----------+
    2 rows in set (0.00 sec)

同样的，向bit类型字段插入字符也会转换成对应的字符集编码再存储：

mysql> insert into test values (3,'a');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select id,bin(col1) from test;
    +----+-----------+
    | id | bin(col1) |
    +----+-----------+
    |  1 | 1         |
    |  2 | 10        |
    |  3 | 1100001   |
    +----+-----------+
    3 rows in set (0.00 sec)