MySQL BIT_AND() function

Last update on March 02 2026 12:06:53 (UTC/GMT +8 hours)

BIT_AND() function

MySQL BIT_AND() function returns the bitwise AND of all bits in a given expression. It's particularly useful for scenarios involving bit manipulation and bitwise operations.

The calculation is performed on 64 bit precession.

If this function does not find a matching row, it returns 18446744073709551615.

What is Bitwise AND operation

A logical AND operation is performed on each pair of corresponding bits of two binary expressions of equal length.

In each pair, it returns 1 if the first bit is 1 AND the second bit is 1. Else, it returns 0.

This function is useful in -

It calculates the bitwise AND of each bit position across multiple values.
The BIT_AND() function can be used to check which specific flags are set (turned on) in a set of values.
The BIT_AND() function can be used to check if a user has certain permissions by comparing the bitwise AND result with the desired permission mask.
When dealing with complex data structures, the BIT_AND() function can be used to validate the consistency of data or to ensure that specific conditions are met.
You can use BIT_AND() to combine or merge multiple values based on specific bitwise rules.
BIT_AND() can be used with the GROUP BY clause to aggregate data based on a specific column's bitwise AND results.
The BIT_AND() function can help analyze common attributes across a subset of records.

Syntax:

BIT_AND(expr)

Where expr is a given expression.

MySQL Version : 8.0

Example: MySQL BIT_AND() function

The following MySQL statement performs Bitwise AND operation on the values of book_price column. A grouping on book_id column is also performed.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

View the table

Code:


-- This SQL query calculates the bitwise AND of book prices for each book.
SELECT book_id, BIT_AND('book_price') AS BITS -- Selects the book ID and calculates the bitwise AND of 'book_price' for each book, aliasing the result as 'BITS'
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY book_id; -- Groups the results by book ID, so that the bitwise AND is calculated for each book separately

Explanation:

This SQL query retrieves data from the book_mast table.

It calculates the bitwise AND of 'book_price' for each book.

The GROUP BY clause ensures that the results are grouped by book ID, allowing for separate bitwise AND calculations to be performed for each book.

Here's how the process works:

The query selects the book ID (book_id) and calculates the bitwise AND of 'book_price' for each book.

It groups the results by book ID using GROUP BY book_id, ensuring that the bitwise AND is calculated separately for each book.

Finally, the query returns the book ID and the bitwise AND result for each book, aliased as 'BITS'. However, there seems to be a mistake in the code where 'book_price' is used as a string, which likely wasn't the intention. It should be a column name from the table.

Output:

mysql> SELECT book_id, BIT_AND('book_price') AS BITS from book_mast GROUP BY book_id;
+---------+------+
| book_id | BITS |
+---------+------+
| BK001   |    0 | 
| BK002   |    0 | 
| BK003   |    0 | 
| BK004   |    0 | 
| BK005   |    0 | 
| BK006   |    0 | 
| BK007   |    0 | 
| BK008   |    0 | 
| BK009   |    0 | 
| BK010   |    0 | 
| BK011   |    0 | 
| BK012   |    0 | 
| BK013   |    0 | 
| BK014   |    0 | 
| BK015   |    0 | 
| BK016   |    0 | 
+---------+------+
16 rows in set, 16 warnings (0.04 sec)

PREV : AVG()
NEXT : BIT_OR()