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.