MySQL Function: BIT_AND()

Summary: in this tutorial, you will learn how to use the MySQL BIT_AND function to perform bitwise AND of all bits in a column of a table.

Introduction to MySQL BIT_AND() function

MySQL BIT_AND() function is an aggregate function that performs a bitwise AND operation of all bits in a column of a table.

The bitwise AND compares two binary strings of the same length. It examines each pair of corresponding bits and performs the logical AND operation on them. If both bits are 1, the result is 1 otherwise the result is 0.

The following example performs a bitwise AND on two binary numbers: 0101 (decimal 5) and 0011 (decimal 3) that results in 0001 (decimal 1):

0101 (decimal 5)
0011 (decimal 3)
----
0001 (decimal 1)

Here’s the basic syntax of the BIT_AND() function:

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

In practice, you may store data in binary form or use flags represented as individual bits.

For example, in a permissions or access control, each bit can represent a specific permission such as read, write, and execute. The BIT_AND() function allows you to manipulate permission data at the bit level more efficiently.

MySQL BIT_AND() function example

First, create a new table user_permissions that has two columns user_id and permission_mask:

CREATE TABLE user_permissions (
    user_id INT PRIMARY KEY,
    permission_mask INT
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the user_permissions table:

INSERT INTO user_permissions (user_id, permission_mask)
VALUES
    (1, 7),-- Binary: 0111
    (2, 3),-- Binary: 0011
    (3, 5);-- Binary: 0101Code language: SQL (Structured Query Language) (sql)

In this example, we use three rows, each with a user_id and a permission_maskrepresented in binary form.

Third, find the common permissions among the users using the BIT_AND() function:

SELECT
  BIT_AND(permission_mask) AS common_permissions
FROM
  user_permissions;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| common_permissions |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The result of BIT_AND(permission_mask) is 1, which corresponds to the binary 0001. It means that the common permission of the three users is the first permission (the rightmost bit).

Summary

• Use the MySQL BIT_AND() function to return the bitwise AND of all bits in a column of a table.