1.6.5 BIT_COUNT和BIT_OR

下面的示例演示如何使用位组函数计算用户访问Web页面的每个月的天数：

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

示例表包含用户访问页面的年月日值。要确定每个月发生的访问次数，请使用此查询：

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
    ->        GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+
2 rows in set (0.00 sec)

这里使用 BIT_COUNT 和 BIT_OR，非常优雅的解决了这个问题。我们分析下这个过程。

我们先看下面的语句，先忽略掉 GROUP BY year, month

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    5 |
+------+-------+------+
1 row in set (0.00 sec)

BIT_COUNT( expr )：返回 expr 的二进制表达式中”1“的个数。

BIT_OR( expr )：返回 expr 中所有比特的位 OR 操作。

mysql> select year,month, day, 1<<day FROM t1;
+------+-------+------+------------+
| year | month | day  | 1<<day     |
+------+-------+------+------------+
| 2000 |    01 |   01 |          2 | 二进制 10 ＝ 1 << 01
| 2000 |    01 |   20 |    1048576 | 二进制 100000000000000000000 ＝ 1 << 20
| 2000 |    01 |   30 | 1073741824 | 二进制 1000000000000000000000000000000 ＝ 1 << 30
| 2000 |    02 |   02 |          4 | 二进制 100 ＝ 1 << 02
| 2000 |    02 |   23 |    8388608 | 二进制 100000000000000000000000 ＝ 1 << 23
| 2000 |    02 |   23 |    8388608 | 二进制 100000000000000000000000 ＝ 1 << 23
+------+-------+------+------------+
6 rows in set (0.00 sec)

使用 BIT_OR 对所有结果进行 OR 操作。

mysql> select year,month, day, BIT_OR(1<<day) FROM t1;
+------+-------+------+----------------+
| year | month | day  | BIT_OR(1<<day) |
+------+-------+------+----------------+
| 2000 |    01 |   01 |     1083179014 | 二进制 100 0000 1001 0000 0000 0000 0000 0110
+------+-------+------+----------------+
1 row in set (0.00 sec)

使用 BIT_COUNT 统计返回表达式中 “1” 的个数。

mysql> select year,month, day, BIT_COUNT(BIT_OR(1<<day)) FROM t1;
+------+-------+------+---------------------------+
| year | month | day  | BIT_COUNT(BIT_OR(1<<day)) |
+------+-------+------+---------------------------+
| 2000 |    01 |   01 |                         5 | 表达式中出现了5个 “1”
+------+-------+------+---------------------------+
1 row in set (0.00 sec)

如果语句中有 GROUP BY ，则在 GROUP BY 簇中，进行了如上的操作。