先看看BIT_AND(),BIT_OR(),BIT_XOR()

语法:BIT_AND(expr),BIT_OR(expr),BIT_XOR(expr)

现在有数据表如下:

mysql>CREATE TABLE test (cate VARCHAR(1), number INT);
mysql>INSERT INTO test VALUES 
     ->('a',111),('a',110),('a',100),
     ->('a=b',000),('b',001),('b',011);
mysql>SELECT * FROM test;
+------+--------+
| cate | number | 
+------+--------+
| a    |    111 | 
+------+--------+
| a    |    110 | 
+------+--------+
| a    |    100 | 
+------+--------+
| b    |    000 | 
+------+--------+
| b    |    001 | 
+------+--------+
| b    |    011 | 
+------+--------+

BIT_AND(),BIT_OR(),BIT_XOR()查询结果:

mysql>SELECT cate, BIT_AND(number), BIT_OR(number), BIT_XOR(number) FROM test GROUP BY cate;
+------+-----------------+----------------+-----------------+
| cate | BIT_AND(number) | BIT_OR(number) | BIT_XOR(number) |
+------+-----------------+----------------+-----------------+
| a    |             100 |            111 |             101 |
| b    |               0 |             11 |              10 |
+------+-----------------+----------------+-----------------+

解析:

BIT_AND():按位与

a = 111(b) & 110(b) & 100(b)=100(b);

b = 000(b) & 001(b) & 011(b)=000(b);

BIT_OR():按位或

a = 111(b) | 110(b) | 100(b)=111(b);

b = 000(b) | 001(b) | 011(b)=011(b);

BIT_XOR():按位异或

a = 111(b) ^ 110(b) ^ 100(b)=101(b);

b = 000(b) ^ 001(b) ^ 011(b)=010(b);

关于位运算是如何进行的,请参考上一篇文章:与(&)、或(|)、异或(^) – 位运算详解

再来看看BIT_COUNT()

语法:BIT_COUNT( expr )

例:

mysql>SELECT BIT_COUNT(7) AS a,BIT_COUNT(8) AS b;
+---+---+
| a | b |
+---+---+
| 3 | 1 |
+---+---+

解析:按位统计1的数量

7(d)=111(b),所以BIT_COUNT(7)=3

8(d)=1000(b),所以BIT_COUNT(8)=1

发表评论

电子邮件地址不会被公开。 必填项已用*标注