MySQL - BIT_AND() Function



The MySQL BIT_AND() function is used to calculate the bit-wise AND operation on the values in a particular column.

Following is the table representation of BIT_AND() function −

A B Result
1 1 1
0 0 0
1 0 0

A bit-wise AND operator will compare one bit with another bit. If both these bits are 1, the result will be set to 1. If either one of these bits is 0, the result will be set to 0.

Let us consider a simple example scenario, where we perform a bitwise AND operation on the integer values 4, 5, and 9 −

4 (bit value 0100)
5 (bit value 0101)
9 (bit value 1001)

This function will first convert the integer values in to bit values and then it performs the bitwise-and operation and returns the result as an integer.

0100 (integer value 4)
0101 (integer value 5)
----
0100 (Result of bit value 4 and 2)
1001 (integer value 7)
----
0000 (Final Result is 0)

Syntax

Following is the syntax of MySQL BIT_AND() function −

BIT_AND(expr);

Parameters

This method accepts only one parameter. The same is described below −

  • expr: Numeric expressions for which the bitwise AND operation will be performed.

Return value

This function returns performs the bitwise-and operation on the input values and returns the result as an integer.

Example

First of all, let us create a table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   RANKING INT,
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,RANKING) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 5 ),
(2, 'Khilan', 25, 'Delhi', 3 ),
(3, 'Kaushik', 23, 'Kota', 6 ),
(4, 'Chaitali', 25, 'Mumbai', 4 ),
(5, 'Hardik', 27, 'Bhopal', 2 ),
(6, 'Komal', 22, 'Hyderabad', 7 ),
(7, 'Muffy', 24, 'Indore', 1 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS RANKING
1 Ramesh 32 Ahmedabad 5
2 Khilan 25 Delhi 3
3 Kaushik 23 Kota 6
4 Chaitali 25 Mumbai 4
5 Hardik 27 Bhopal 2
6 Komal 22 Hyderabad 7
7 Muffy 24 Indore 1

Here, we are using the MySQL BIT_AND() function to calculate the bitwise-and operation on the values of the column RANKING

SELECT BIT_AND(RANKING) From CUSTOMERS;

This will produce the following result −

BIT_AND(RANKING)
0

Now, we are calculating the bitwise-and operation on the values of the column AGE

SELECT BIT_AND(AGE) From CUSTOMERS;

The output for the query above is produced as given below −

BIT_AND(AGE)
0
Advertisements