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:
-- 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)