SQL函数BIT_AND、BIT_OR、BIT_XOR和BIT_COUNT的最简明详解

一、创建表代码

create table bits(
    id int unsigned primary key auto_increment,
    bin_value bit(8)
);

二、插入二进制数据代码

insert into bits(bin_value) values(b'101'),(b'110');

三、运行结果

mysql> create table bits(
    ->     id int unsigned primary key auto_increment,
    ->     bin_value bit(8)
    -> );
Query OK, 0 rows affected (0.52 sec)

mysql> insert into bits(bin_value) values(b'101'),(b'110');
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

#3.1表情况
mysql> select * from bits;
+----+----------------------+
| id | bin_value            |
+----+----------------------+
|  1 | 0x05                 |
|  2 | 0x06                 |
+----+----------------------+
2 rows in set (0.00 sec)

mysql> select id, bin(bin_value) from bits;
+----+----------------+
| id | bin(bin_value) |
+----+----------------+
|  1 | 101            |
|  2 | 110            |
+----+----------------+
2 rows in set (0.00 sec)

#3.2逻辑与,bit_and()
mysql> select bit_and(bin(bin_value)) from bits;
+-------------------------+
| bit_and(bin(bin_value)) |
+-------------------------+
|                     100 |
+-------------------------+
1 row in set (0.00 sec)
解析：
bit_and():逻辑与
id  bin_value
1   101
2   110
    100     bit_and()，逻辑与的结果

#3.3逻辑或,bit_or()
mysql> select bit_or(bin(bin_value)) from bits;
+------------------------+
| bit_or(bin(bin_value)) |
+------------------------+
|                    111 |
+------------------------+
1 row in set (0.00 sec)
解析：
bit_or():逻辑或
id  bin_value
1   101
2   110
    111     bit_or()，逻辑或的结果

#3.4逻辑异或,bit_xor()
mysql> select bit_xor(bin(bin_value)) from bits;
+-------------------------+
| bit_xor(bin(bin_value)) |
+-------------------------+
|                      11 |
+-------------------------+
1 row in set (0.00 sec)
解析：
bit_xor():逻辑异或
id  bin_value
1   101
2   110
     11     bit_xor():逻辑异或的结果

#bit_count()
mysql> select bit_count(b'101');
+-------------------+
| bit_count(b'101') |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.00 sec)

mysql> select bit_count(5);
+--------------+
| bit_count(5) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)
解析：
bit_count()函数用于返回给定输入中有效的位数。活动位可以算作二进制数中的1个数；
5(d)=101(b),所以bit_count(b'101')=bit_count(5)=2