Bit Functions
MySQL bit-functions are used to perform operations on binary data at the bit level. These functions are used for tasks that require binary data organization or manipulation based on bitwise logic.
Syntax
These are some commonly used bit-functions in MySQL:
Bitwise AND &()
: It takes two integer operands and compares them bit by bit.
The result is 1 if the bits of both operands are 1, otherwise, the result is 0.
SELECT (expression1 & expression2);
Bitwise XOR ^()
: It takes two integer values and executes a bitwise exclusive OR operation on them.
SELECT (expression1 ^ expression2);
Bitwise OR |
: It takes two operands and compares them by the bit. If at least one of the corresponding bits is 1, the result is 1. If both bits are 0, the result is 0.
SELECT (expression1 | expression2);
BIT_COUNT
: This function is used to return the number of bits that are set to 1 in the operand’s binary representation.
SELECT BIT_COUNT(expression);
Examples
Next are a few examples of some of the MySQL concepts described. In this basic example, mySQL performs the bitwise AND operation on the two literal integer values 45 and 25:
> SELECT 45 & 25;-> 9;
In the above example:
- 45 in binary is represented as 101101.
- 25 in binary is represented as 011001.
- The bitwise AND operation between 45 and 25 is in binary 001001.
- 001001 in decimal is represented as 9.
In the example below, the function XOR
is used to compare and manipulate the label values within a database. The query uses this function to select rows based on the results of the operation:
SELECT *FROM postsWHERE (labels ^ 3) > 5;
From the table, a query takes rows from the name where the XOR comparison of labels with 3 results in a value greater than 5.
id | name | labels |
---|---|---|
1 | post1 | 5 |
2 | post2 | 3 |
3 | post3 | 12 |
Performed XOR operations are explained bellow:
Labels value 5 is represented by 0101 and XOR comparison is:
- 0101 ^ 0011 = 0110 (6 in decimal, which is greater than 5)
Labels value 3 is represented by 0011 and XOR comparison is:
- 0011 ^ 0011 = 0000 (0 in decimal, which is not greater than 5)
Labels value 12 is represented by 1100 and XOR comparison is:
- 1100 ^ 0011 = 1111 (15 in decimal, which is greater than 5)
The result looks like:
id | name | labels |
---|---|---|
1 | post1 | 5 |
3 | post3 | 12 |
This basic XOR
operation example illustrates a bit-level comparison of two integer literals (5 and 0):
> SELECT 5 ^ 0;-> 5;
Comparison of each bit position:
If the bits are different, the resulting bit is 1
If the bits are the same, the resulting bit is 0
5 in binary is represented as 101 and 0 in binary is represented as 000.
Bit Position 2 is is the leftmost bit in a 3-bit binary number
Bit Position 1 is the middle bit in a 3-bit binary number
Bit Position 0 is the rightmost bit in a 3-bit binary number
When comparing these two integer literals, the result is 5:
Bit Position | 2 1 0 |
---|---|
5 (decimal) | 1 0 1 |
0 (decimal) | 0 0 0 |
XOR Result | 1 0 1 |
In next example is needed to perform the bitwise OR operation between the decimal integers 9 and 5 retrieved from a table.
SELECT (9 | 5) AS result;
- 9 in binary: 1001
- 5 in binary: 0101 The result of the bitwise OR operation is 1101 in binary, which is equivalent to the decimal number 13.
In this example, we have a table called customers which contains a list of customers IDs as integers. Each customer ID is associated with a number of orders. We need to find out how many bits are set to ‘1’ in the binary representation of each customer’s ordering activity.
customer_ID | number_of_orders |
---|---|
102 | 5 |
102 | 7 |
103 | 10 |
104 | 12 |
The BIT_COUNT(number_of_orders)
function returns the number of ‘1’ bits in the binary representation of the number_of_orders column.
SELECTcustomer_id,number_of_orders,BIT_COUNT(number_of_orders) AS bits_set_to_1FROMcustomers;
- For value 5 (binary 101), the number of ‘1’ bits is 2.
- For value 7 (binary 111), the number of ‘1’ bits is 3.
- For value 10 (binary 1010), the number of ‘1’ bits is 2.
- For value 12 (binary 1100), the number of ‘1’ bits is 2.
customer_ID | number_of_orders | bits_set_to_1 |
---|---|---|
102 | 5 | 2 |
102 | 7 | 3 |
103 | 10 | 2 |
104 | 12 | 2 |
The result returns how many bits are set to ‘1’ in the binary representation of each customer’s number of orders.
All contributors
- Anonymous contributor
Contribute to Docs
- Learn more about how to get involved.
- Edit this page on GitHub to fix an error or make an improvement.
- Submit feedback to let us know how we can improve Docs.