MySQL BIT_AND() function

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

Code:

SELECT book_id, BIT_AND('book_price') AS BITS
FROM book_mast group by book_id;

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)

Previous: AVG()
Next: BIT_OR()