语法: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( 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
本文采用 「CC BY-NC-SA 4.0」创作共享协议,转载请标注以下信息:
原文出处:Yiiven https://cloud.tencent.com/developer/article/2193173