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

XOR is an exclusive OR, and so a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

Syntax

The syntax goes like this:

BIT_XOR(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_XOR() function to return the bitwise XOR of all bits in that column:

SELECT BIT_XOR(c2)
FROM t1;

Result:

417

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

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

Result:

+------+-------------+
| c1   | BIT_XOR(c2) |
+------+-------------+
| Cat  |         182 |
| Cow  |          62 |
| Dog  |         297 |
+------+-------------+

Window Function

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

Result:

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

More Information

See the MySQL documentation for more information about this function.