MySQL BIT_XOR() Function

Summary: in this tutorial, you will learn how to use the MySQL BIT_XOR function to perform a bitwise XOR on the values in a column of a table.

Introduction to MySQL BIT_XOR function

The BIT_XOR() function is an aggregate function that performs an XOR bitwise operation on values in a column of a table.

Here’s the syntax of the BIT_XOR() function:

SELECT BIT_XOR(column_name)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

If the column_name has no value, the BIT_XOR returns a result where all bits are set to zero. Also, the result will have the same number of bits as the input values.

The bitwise XOR takes two-bit strings with the same length and performs a logical exclusive OR operation on each pair of the corresponding bits. It returns 1 if only one of the bits is 1 and 0 if both bits are 0 or both bits are 1.

For example, the following shows how to perform a bitwise XOR operation on the bit values 0101 and 0011, which results in 0110.

0101 (decimal 5)
0011 (decimal 3)
0110 (decimal 6)Code language: SQL (Structured Query Language) (sql)

MySQL BIT_XOR function example

First, create a new table called devices that stores the binary status flags for electronic devices:

CREATE TABLE devices (
    device_id INT PRIMARY KEY,
    status_flags INT
);Code language: SQL (Structured Query Language) (sql)

In this table, device_id uniquely identifies each device, and status_flags stores the device’s status flags as binary values.

Second, insert three rows into the devices table:

INSERT INTO devices (device_id, status_flags)
VALUES
    (1, 6),   -- Binary: 0110
    (2, 3),   -- Binary: 0011
    (3, 5);   -- Binary: 0101Code language: SQL (Structured Query Language) (sql)

Third, find the exclusive status flags among a group of devices using the BIT_XOR function:

SELECT 
  BIT_XOR(status_flags) AS exclusive_flags 
FROM 
  devices 
WHERE 
  device_id IN (1, 2, 3);Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| exclusive_flags |
+-----------------+
|               0 |
+-----------------+Code language: SQL (Structured Query Language) (sql)

The result is zero indicating that there are no common status flags among devices. In other words, each device has its unique status.

Summary

• Use MySQL BIT_XOR() function to perform a bitwise XOR operation on the values in a column of a table.