Bit functions-PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X supports bitwise operations through both scalar functions and aggregate functions.

Scalar functions

Function	Description
`\|`	Bitwise OR
`^`	Bitwise exclusive OR (XOR)
`&`	Bitwise AND
`BIT_COUNT()`	Returns the number of 1s in the binary representation of a number

How bitwise operators work

Each operator compares the corresponding bits of two numbers:

`&` (AND) — a bit is 1 only when both input bits are 1:

  0011  (3)
& 0010  (2)
  ----
  0010  (2)

`|` (OR) — a bit is 1 when at least one input bit is 1:

  0011  (3)
| 0010  (2)
  ----
  0011  (3)

`^` (XOR) — a bit is 1 only when the two input bits differ:

  0011  (3)
^ 0010  (2)
  ----
  0001  (1)

BIT_COUNT()

BIT_COUNT(expr) returns the number of 1s in the binary representation of expr. Returns NULL if expr is NULL.

mysql> SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
+--------------+----------------------+
| BIT_COUNT(29) | BIT_COUNT(b'101010') |
+--------------+----------------------+
|            4 |                    3 |
+--------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT BIT_COUNT(NULL);
+-----------------+
| BIT_COUNT(NULL) |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

Note: When passing a binary number, prefix it with b, for example b'101010'. Without the prefix, the function treats the argument as a decimal string and returns a different result. For example, BIT_COUNT('101010') returns 8 because the string '101010' is interpreted as the decimal integer 101010, not as a binary number.

mysql> SELECT 3 & 2;
+-------+
| 3 & 2 |
+-------+
|     2 |
+-------+
1 row in set (0.01 sec)

Aggregate functions

Function	Description
`BIT_OR()`	Bitwise OR of all values in the group
`BIT_AND()`	Bitwise AND of all values in the group
`BIT_XOR()`	Bitwise XOR of all values in the group

Use these functions to combine a set of integer values with bitwise operations across rows. They work with GROUP BY to compute per-group results.

CREATE TABLE access_flags (role VARCHAR(20), perms INT);
INSERT INTO access_flags VALUES
  ('admin', 5),   -- 0101
  ('admin', 3),   -- 0011
  ('user',  6),   -- 0110
  ('user',  4);   -- 0100

mysql> SELECT role, BIT_AND(perms), BIT_OR(perms), BIT_XOR(perms)
       FROM access_flags GROUP BY role;
+-------+----------------+---------------+----------------+
| role  | BIT_AND(perms) | BIT_OR(perms) | BIT_XOR(perms) |
+-------+----------------+---------------+----------------+
| admin |              1 |             7 |              6 |
| user  |              4 |             6 |              2 |
+-------+----------------+---------------+----------------+
2 rows in set (0.00 sec)