In MySQL, the BIT_AND() function returns the bitwise AND of all bits in a given expression.

Syntax

The syntax goes like this:

BIT_AND(expr) [over_clause]

Where expr is the target column or expression that the function operates on.

The [over_clause] part means that we also have the option of providing an over clause to make the function execute as a window function.

Example

Suppose we create the following table and insert data:

CREATE TABLE t1(
  c1 VARCHAR(20),
  c2 INT
);

INSERT INTO t1 VALUES
  ('Cat', 111),
  ('Dog', 353),
  ('Cow', 93),
  ('Cat', 217),
  ('Dog', 72),
  ('Cow', 99);

So the c2 column contains a bunch of integers.

The following query is an example of using the BIT_AND() function to return the bitwise AND of all bits in that column:

SELECT BIT_AND(c2)
FROM t1;

Result:

64

Let’s add a GROUP BY clause to group by c1:

SELECT 
    c1,
    BIT_AND(c2)
FROM t1
GROUP BY c1
ORDER BY c1;

Result:

+------+-------------+
| c1   | BIT_AND(c2) |
+------+-------------+
| Cat  |          73 |
| Cow  |          65 |
| Dog  |          64 |
+------+-------------+

Window Function

From MySQL 8.0.12, we can provide an OVER clause in order to make BIT_AND() operate as a window function.

Suppose we create and populate the following table:

CREATE TABLE t2 (c1 INT, c2 INT);

INSERT INTO t2 VALUES 
(7,1), (8,3), (1,3), (4,9), (4,5), (11,3), (6,1);

Here’s an example of a query that uses the OVER clause:

SELECT 
    c1, 
    BIT_AND(c2)
 OVER (ORDER BY c1) AS bit_and
 FROM t2;

Result:

+------+---------+
| c1   | bit_and |
+------+---------+
|    1 |       3 |
|    4 |       1 |
|    4 |       1 |
|    6 |       1 |
|    7 |       1 |
|    8 |       1 |
|   11 |       1 |
+------+---------+

More Information

See the MySQL documentation for more information about this function.