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

Syntax

The syntax goes like this:

BIT_OR(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_OR() function to return the bitwise OR of all bits in that column:

SELECT BIT_OR(c2)
FROM t1;

Result:

511

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

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

Result:

+------+------------+
| c1   | BIT_OR(c2) |
+------+------------+
| Cat  |        255 |
| Cow  |        127 |
| Dog  |        361 |
+------+------------+

Window Function

From MySQL 8.0.12, we can provide an OVER clause in order to make BIT_OR() 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_OR(c2)
 OVER (ORDER BY c1) AS bit_and
 FROM t2;

Result:

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

More Information

See the MySQL documentation for more information about this function.