• MySQL bit类型解析

    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”表示缺席,这样就更容易识别每个人出席或缺席的日子。

    1. MySQL> DROP TABLE IF EXISTS attendance;
    2. MySQL> CREATE TABLE attendance (
    3. emp_no  CHAR(3),
    4. emp_name CHAR(50),
    5. attend BIT(5),
    6. class  INT,
    7. KEY `idx_bit` (`attend`)
    8. );
    9.  
    10. #使用b'val'的编写方式, Val是使用0和1编写的二进制值
    11. MySQL> INSERT INTO attendance (emp_no, emp_name, attend, class)
    12. VALUES ('001','Jim',b'11111',5),('002','Kim',b'11000',5),('003','Cui',b'00111',5),
    13. ('004','King',b'11101',5),('005','Wang',b'101',5),('006','Chen',NULL,5),
    14. ('007','Piao',0,5),('008','Hu',1,5);
    15.  
    16. MySQL> SELECT  emp_no,emp_name,attend ,class  FROM  attendance;
    17. +--------+----------+----------------+-------+
    18. | emp_no | emp_name | attend         | class |
    19. +--------+----------+----------------+-------+
    20. | 001    | Jim      | 0x1F           |     5 |
    21. | 002    | Kim      | 0x18           |     5 |
    22. | 003    | Cui      | 0x07           |     5 |
    23. | 004    | King     | 0x1D           |     5 |
    24. | 005    | Wang     | 0x05           |     5 |
    25. | 006    | Chen     | NULL           |     5 |
    26. | 007    | Piao     | 0x00           |     5 |
    27. | 008    | Hu       | 0x01           |     5 |
    28. +--------+----------+----------------+-------+
    29. 8 rows in set (0.00 sec)
    30.  
    31.  
    32. #BIN方式显示
    33. MySQL> SELECT  emp_no,emp_name,BIN(attend) ,class  FROM  attendance;
    34. +--------+----------+-------------+-------+
    35. | emp_no | emp_name | BIN(attend) | class |
    36. +--------+----------+-------------+-------+
    37. | 001    | Jim      | 11111       |     5 |
    38. | 002    | Kim      | 11000       |     5 |
    39. | 003    | Cui      | 111         |     5 |
    40. | 004    | King     | 11101       |     5 |
    41. | 005    | Wang     | 101         |     5 |
    42. | 006    | Chen     | NULL        |     5 |
    43. | 007    | Piao     | 0           |     5 |
    44. | 008    | Hu       | 1           |     5 |
    45. +--------+----------+-------------+-------+
    46. 8 rows in set (0.00 sec)
    47.  
    48.  
    49. #字符串左填充函数LPAD
    50. MySQL> SELECT  emp_no,emp_name,LPAD(BIN(attend),5,0) ,class  FROM  attendance;
    51. +--------+----------+-----------------------+-------+
    52. | emp_no | emp_name | LPAD(BIN(attend),5,0) | class |
    53. +--------+----------+-----------------------+-------+
    54. | 001    | Jim      | 11111                 |     5 |
    55. | 002    | Kim      | 11000                 |     5 |
    56. | 003    | Cui      | 00111                 |     5 |
    57. | 004    | King     | 11101                 |     5 |
    58. | 005    | Wang     | 00101                 |     5 |
    59. | 006    | Chen     | NULL                  |     5 |
    60. | 007    | Piao     | 00000                 |     5 |
    61. | 008    | Hu       | 00001                 |     5 |
    62. +--------+----------+-----------------------+-------+
    63. 8 rows in set (0.00 sec)

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

    数据查询：

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

    1. ##1.采用INT类型数字进行查询
    2. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
    3.         FROM  attendance where attend=7;
    4. +--------+----------+-------------+-------------+----------------+-------+
    5. | emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
    6. +--------+----------+-------------+-------------+----------------+-------+
    7. | 003    | Cui      | 111         | 7           | 0x07           |     5 |
    8. +--------+----------+-------------+-------------+----------------+-------+
    9.  
    10. ##2.采用位置进行查询
    11. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
    12.         FROM  attendance where attend=b'111';
    13. +--------+----------+-------------+-------------+----------------+-------+
    14. | emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
    15. +--------+----------+-------------+-------------+----------------+-------+
    16. | 003    | Cui      | 111         | 7           | 0x07           |     5 |
    17. +--------+----------+-------------+-------------+----------------+-------+
    18.  
    19. ##3.string类型进行查询
    20. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
    21.               FROM  attendance where attend='7';
    22. Empty set (0.00 sec)
    23.  
    24. ##4.in 语句部分失效（字符串）
    25. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class                  
    26.        FROM  attendance WHERE attend in('7',5);
    27. +--------+----------+-------------+-------------+----------------+-------+
    28. | emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
    29. +--------+----------+-------------+-------------+----------------+-------+
    30. | 003    | Cui      | 111         | 7           | 0x07           |     5 |
    31. +--------+----------+-------------+-------------+----------------+-------+
    32.  
    33. ##5. NULL字段查询
    34. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
    35.        FROM  attendance WHERE attend IS NULL;
    36. +--------+----------+-------------+-------------+----------------+-------+
    37. | emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class |
    38. +--------+----------+-------------+-------------+----------------+-------+
    39. | 006    | Chen     | NULL        | NULL        | NULL           |     5 |
    40. +--------+----------+-------------+-------------+----------------+-------+
    41. 1 row in set (0.00 sec)

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

    索引

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

    1. #数字类型
    2. MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
    3.         FROM  attendance where attend=7;
    4. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    5. | id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    6. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    7. |  1 | SIMPLE      | attendance | NULL       | ref  | idx_bit       | idx_bit | 2       | const |    1 |   100.00 | NULL  |
    8. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    9. 1 row in set, 1 warning (0.00 sec)
    10.  
    11. ##2.采用位置进行查询
    12. MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  FROM  attendance where attend=b'111';
    13. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    14. | id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    15. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    16. |  1 | SIMPLE      | attendance | NULL       | ref  | idx_bit       | idx_bit | 2       | const |    1 |   100.00 | NULL  |
    17. +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    18. 1 row in set, 1 warning (0.00 sec)
    19.  
    20.  
    21. ##3.范围
    22. MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class                    
    23.        FROM  attendance WHERE attend>17;
    24. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    25. | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
    26. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    27. |  1 | SIMPLE      | attendance | NULL       | range | idx_bit       | idx_bit | 2       | NULL |    3 |   100.00 | Using index condition |
    28. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    29. 1 row in set, 1 warning (0.00 sec)
    30.  
    31. MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class  
    32.        FROM  attendance WHERE attend>b'111';
    33. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    34. | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
    35. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    36. |  1 | SIMPLE      | attendance | NULL       | range | idx_bit       | idx_bit | 2       | NULL |    3 |   100.00 | Using index condition |
    37. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
    38. 1 row in set, 1 warning (0.01 sec)
    39.  
    40.  
    41. ##5.采用string类型字段
    42. MySQL> EXPLAIN SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class           
    43.        FROM  attendance WHERE attend='7';
    44. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    45. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
    46. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    47. |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
    48. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
    49. 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之间可能存在不兼容的信息。

    1. #BIT_COUNT(）
    2. MySQL> SELECT  emp_no,emp_name,BIN(attend),HEX(attend),attend ,class,bit_count(attend)  FROM  attendance;
    3. +--------+----------+-------------+-------------+----------------+-------+-------------------+
    4. | emp_no | emp_name | BIN(attend) | HEX(attend) | attend         | class | bit_count(attend) |
    5. +--------+----------+-------------+-------------+----------------+-------+-------------------+
    6. | 001    | Jim      | 11111       | 1F          | 0x1F           |     5 |                 5 |
    7. | 002    | Kim      | 11000       | 18          | 0x18           |     5 |                 2 |
    8. | 003    | Cui      | 111         | 7           | 0x07           |     5 |                 3 |
    9. | 004    | King     | 11101       | 1D          | 0x1D           |     5 |                 4 |
    10. | 005    | Wang     | 101         | 5           | 0x05           |     5 |                 2 |
    11. | 006    | Chen     | NULL        | NULL        | NULL           |     5 |              NULL |
    12. | 007    | Piao     | 0           | 0           | 0x00           |     5 |                 0 |
    13. | 008    | Hu       | 1           | 1           | 0x01           |     5 |                 1 |
    14. +--------+----------+-------------+-------------+----------------+-------+-------------------+
    15. 8 rows in set (0.00 sec)
    16.  
    17. #UUID 和 IPV6的一些转换，因为包含一些“-”，“：：”的符号
    18. MySQL> SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
    19. +----------------------------------------------------------+
    20. | HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db')) |
    21. +----------------------------------------------------------+
    22. | 6CCD780CBABA102695645B8C656024DB                         |
    23. +----------------------------------------------------------+
    24.  
    25. #IPV6
    26. MySQL> SELECT HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72'));
    27. +---------------------------------------------+
    28. | HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72')) |
    29. +---------------------------------------------+
    30. | FE800000000000000219D1FFFE911A72            |
    31. +---------------------------------------------+

    总结

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

    • 使用场景，出勤率类似场景 + BIT_COUNT统计。
    • bit函数方面最实用的是UUID ，IPV6的处理。
    • 数据长度和存储方面，普遍下最小长度，0和1组合底层数据结构。
    • 索引方面构造还是遵守整数型的Btree。
  • 相关阅读:
    分布式事务 —— SpringCloud Alibaba Seata
    英国入境前需要准备什么？
    配置云服务器(Ubuntu)的vnc守护进程(服务)
    (欧拉)openEuler系统添加网卡文件配置流程、(欧拉)openEuler系统手动配置ipv6地址流程、(欧拉)openEuler系统网络管理说明
    【408篇】C语言笔记-第十章（线性表）
    快速部署 MySQL InnoDB Cluster
    C++中promise和future详解
    C语言之数组练习题
    Python深度学习实战-基于class类搭建BP神经网络实现分类任务（附源码和实现效果）
    你可能从未使用过的13个Python特性
  • 原文地址：https://blog.csdn.net/dreamyuzhou/article/details/125535450