MySQL bit类型解析

原创 CuiHulong 2022-06-30

6765

BIT类型介绍

BIT数据类型用于存储位值。其数据有两种取值：0和1，这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。更像最原始的计算机语言编程。

在MySQL里BIT 归纳为Numeric Data类型。BIT数据类型存储位值，支持MyISAM、MEMORY、InnoDB、NDB表。

在数据类型中，bit应该占据空间最小。

BIT(M)类型允许存储M位值。M取值范围为1 ~ 64。NDB集群中所有BIT列的最大总和不能超过4096位

如果将一个值赋给长度小于M位的BIT(M)列，则该值将在左侧填充0。
例如: 给BIT(6)列赋值b’101’实际上等同于给b’000101’赋值。

那BIT场景什么环境下比较适合，实际场景中，一个典型的案例，出勤率。“1”表示出席，“0”表示缺席,这样就更容易识别每个人出席或缺席的日子。

MySQL> DROP TABLE IF EXISTS attendance;
MySQL> CREATE TABLE attendance (
emp_no  CHAR(3),
emp_name CHAR(50),
attend BIT(5),
class  INT,
KEY `idx_bit` (`attend`)
);

#使用b'val'的编写方式, Val是使用0和1编写的二进制值
MySQL> INSERT INTO attendance (emp_no, emp_name, attend, class)
VALUES ('001','Jim',b'11111',5),('002','Kim',b'11000',5),('003','Cui',b'00111',5),
('004','King',b'11101',5),('005','Wang',b'101',5),('006','Chen',NULL,5),
('007','Piao',0,5),('008','Hu',1,5);

MySQL> SELECT  emp_no,emp_name,attend ,class  FROM  attendance;
+--------+----------+----------------+-------+
| emp_no | emp_name | attend         | class |
+--------+----------+----------------+-------+
| 001    | Jim      | 0x1F           |     5 |
| 002    | Kim      | 0x18           |     5 |
| 003    | Cui      | 0x07           |     5 |
| 004    | King     | 0x1D           |     5 |
| 005    | Wang     | 0x05           |     5 |
| 006    | Chen     | NULL           |     5 |
| 007    | Piao     | 0x00           |     5 |
| 008    | Hu       | 0x01           |     5 |
+--------+----------+----------------+-------+
8 rows in set (0.00 sec)


#BIN方式显示
MySQL> SELECT  emp_no,emp_name,BIN(attend) ,class  FROM  attendance;
+--------+----------+-------------+-------+
| emp_no | emp_name | BIN(attend) | class |
+--------+----------+-------------+-------+
| 001    | Jim      | 11111       |     5 |
| 002    | Kim      | 11000       |     5 |
| 003    | Cui      | 111         |     5 |
| 004    | King     | 11101       |     5 |
| 005    | Wang     | 101         |     5 |
| 006    | Chen     | NULL        |     5 |
| 007    | Piao     | 0           |     5 |
| 008    | Hu       | 1           |     5 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)


#字符串左填充函数LPAD
MySQL> SELECT  emp_no,emp_name,LPAD(BIN(attend),5,0) ,class  FROM  attendance;
+--------+----------+-----------------------+-------+
| emp_no | emp_name | LPAD(BIN(attend),5,0) | class |
+--------+----------+-----------------------+-------+
| 001    | Jim      | 11111                 |     5 |
| 002    | Kim      | 11000                 |     5 |
| 003    | Cui      | 00111                 |     5 |
| 004    | King     | 11101                 |     5 |
| 005    | Wang     | 00101                 |     5 |
| 006    | Chen     | NULL                  |     5 |
| 007    | Piao     | 00000                 |     5 |
| 008    | Hu       | 00001                 |     5 |
+--------+----------+-----------------------+-------+
8 rows in set (0.00 sec)

备注：
可以使用位值函数，并且可以通过十进制、二进制或任何其他数据转换函数检索位值。使用LPAD和BIN函数以适当的格式检索数据。

数据查询：

对应WHERE条件 bit字段必须是b’val’ 或则 int类型

##1.采用INT类型数字进行查询
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
        FROM  attendance where attend=7;
+--------+----------+-------------+-------------+----------------+-------+
| emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
+--------+----------+-------------+-------------+----------------+-------+
| 003    | Cui      | 111         | 7           | 0x07           |     5 |
+--------+----------+-------------+-------------+----------------+-------+

##2.采用位置进行查询
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
        FROM  attendance where attend=b'111';
+--------+----------+-------------+-------------+----------------+-------+
| emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
+--------+----------+-------------+-------------+----------------+-------+
| 003    | Cui      | 111         | 7           | 0x07           |     5 |
+--------+----------+-------------+-------------+----------------+-------+

##3.string类型进行查询
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
              FROM  attendance where attend='7';
Empty set (0.00 sec)

##4.in 语句部分失效（字符串）
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class                  
       FROM  attendance WHERE attend in('7',5);
+--------+----------+-------------+-------------+----------------+-------+
| emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
+--------+----------+-------------+-------------+----------------+-------+
| 003    | Cui      | 111         | 7           | 0x07           |     5 |
+--------+----------+-------------+-------------+----------------+-------+

##5. NULL字段查询
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
       FROM  attendance WHERE attend IS NULL;
+--------+----------+-------------+-------------+----------------+-------+
| emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
+--------+----------+-------------+-------------+----------------+-------+
| 006    | Chen     | NULL        | NULL        | NULL           |     5 |
+--------+----------+-------------+-------------+----------------+-------+
1 row in set (0.00 sec)

备注：bit字段只能是整数类型或 bit类型的才能匹配。在数字范围内 b’val’ 或则整数类型对应等价。
比如，上诉例子7和b’111’是属于等价。对于NULL值，bit类型依然是等于null值

索引

下面对bit字段存在索引下，作为条件下，是否能正使用索引。
如等价查询，范围查询，类型（int，bin，string）

#数字类型
MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
        FROM  attendance where attend=7;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | attendance | NULL       | ref  | idx_bit       | idx_bit | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

##2.采用位置进行查询
MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  FROM  attendance where attend=b'111';
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | attendance | NULL       | ref  | idx_bit       | idx_bit | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


##3.范围
MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class                    
       FROM  attendance WHERE attend>17;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | attendance | NULL       | range | idx_bit       | idx_bit | 2       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
       FROM  attendance WHERE attend>b'111';
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | attendance | NULL       | range | idx_bit       | idx_bit | 2       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)


##5.采用string类型字段
MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
       FROM  attendance WHERE attend='7';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

备注：索引只接受int ， b’val’ 类型

BIT函数和运算符

MySQL 8.0中，位函数和操作符允许二进制字符串类型的参数(binary, VARBINARY和BLOB类型)。这使得能够产生大于64位的返回值，更容易执行位操作。位函数和运算符包括BIT_COUNT()、BIT_AND()、BIT_OR()、BIT_XOR()、&、|、^、~、<<和>>。

注意：MySQL 8.0中对二进制字符串参数的位操作可能会产生与5.7不同的结果。所以MySQL 5.7和8.0之间可能存在不兼容的信息。

#BIT_COUNT(）
MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class,bit_count(attend)  FROM  attendance;
+--------+----------+-------------+-------------+----------------+-------+-------------------+
| emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class | bit_count(attend) |
+--------+----------+-------------+-------------+----------------+-------+-------------------+
| 001    | Jim      | 11111       | 1F          | 0x1F           |     5 |                 5 |
| 002    | Kim      | 11000       | 18          | 0x18           |     5 |                 2 |
| 003    | Cui      | 111         | 7           | 0x07           |     5 |                 3 |
| 004    | King     | 11101       | 1D          | 0x1D           |     5 |                 4 |
| 005    | Wang     | 101         | 5           | 0x05           |     5 |                 2 |
| 006    | Chen     | NULL        | NULL        | NULL           |     5 |              NULL |
| 007    | Piao     | 0           | 0           | 0x00           |     5 |                 0 |
| 008    | Hu       | 1           | 1           | 0x01           |     5 |                 1 |
+--------+----------+-------------+-------------+----------------+-------+-------------------+
8 rows in set (0.00 sec)

#UUID 和 IPV6的一些转换，因为包含一些“-”，“：：”的符号
MySQL> SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
+----------------------------------------------------------+
| HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db')) |
+----------------------------------------------------------+
| 6CCD780CBABA102695645B8C656024DB                         |
+----------------------------------------------------------+

#IPV6
MySQL> SELECT HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72'));
+---------------------------------------------+
| HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72')) |
+---------------------------------------------+
| FE800000000000000219D1FFFE911A72            |
+---------------------------------------------+

总结

在MySQL中按照目前对于bit的理解

使用场景，出勤率类似场景 + BIT_COUNT统计。
bit函数方面最实用的是UUID ，IPV6的处理。
数据长度和存储方面，普遍下最小长度，0和1组合底层数据结构。
索引方面构造还是遵守整数型的Btree。

墨力计划 mysql

最后修改时间：2022-12-27 13:29:24

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

专家专栏 | 崔虎龙（共172篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

MySQL bit类型解析

BIT类型介绍

数据查询：

索引

BIT函数和运算符

总结

文章被以下合辑收录

评论