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)