Bit functions and operators of AnalyticDB for MySQL - - Alibaba Cloud Documentation Center

You can use bit functions and operators to process binary data. This topic describes the syntax of bit functions and operators and provides examples on how to use the bit functions and operators in AnalyticDB for MySQL.

BIT_COUNT: converts an argument to a binary value and returns the number of bits that are set to 1 in the value.
&: bitwise AND.
~: inverts all bits.
|: bitwise OR.
^: bitwise XOR.
>> (BITWISE_RIGHT_SHIFT): shifts a value to the right.
<< (BITWISE_LEFT_SHIFT): shifts a value to the left.

BIT_COUNT

bit_count(bigint x)
bit_count(double x)
bit_count(varchar x)

Description: This function converts an argument to a binary value and returns the number of bits that are set to 1 in the value.
Data type of the return value: BIGINT.

Examples:

select bit_count(2);
+--------------+
| bit_count(2) |
+--------------+
|            1 |

select bit_count(pi());
+-----------------+
| bit_count(pi()) |
+-----------------+
|               2 |

select bit_count('123');
+------------------+| 
bit_count('123') |
+------------------+
|                6 |

&

Description: This function is used for bitwise AND.
Data type of the return value: BIGINT.

Example:

 select 12 & 15;
+---------------------+
| bitwise_and(12, 15) |
+---------------------+
|                  12 |

~

Description: This function inverts all bits.
Data type of the return value: BIGINT.

Example:

select 2 & ~1;
+--------------------------------+
| bitwise_and(2, bitwise_not(1)) |
+--------------------------------+
|                              2 |

|

Description: This function is used for bitwise OR.
Data type of the return value: BIGINT.

Example:

select 29 | 15;
+--------------------+
| bitwise_or(29, 15) |
+--------------------+
|                 31 |

^

Description: This function is used for bitwise XOR.
Data type of the return value: BIGINT.

Example:

select 1 ^ 10;
+--------------------+
| bitwise_xor(1, 10) |
+--------------------+
|                 11 |

>> (BITWISE_RIGHT_SHIFT)

bitwise_right_shift(double x, double y) 
bitwise_right_shift(varchar x, varchar y)
bitwise_right_shift(bigint x, bigint y)

Description: This function shifts a value to the right.
Data type of the return value: BIGINT.

Examples:

select 3 >> 2;
+---------------------------+
| bitwise_right_shift(3, 2) |
+---------------------------+
|                         0 |

select 3.4 >> 23.2;
+--------------------------------+
| bitwise_right_shift(3.4, 23.2) |
+--------------------------------+
|                              0 |

<< (BITWISE_LEFT_SHIFT)

bitwise_left_shift(double x, double y) 
bitwise_left_shift(varchar x, varchar y)
bitwise_left_shift(bigint x, bigint y)

Description: This function shifts a value to the left.
Data type of the return value: BIGINT.

Examples:

SELECT 3 << 2;
+--------------------------+
| bitwise_left_shift(3, 2) |
+--------------------------+
|                       12 |

select '3' << '2';
+------------------------------+
| bitwise_left_shift('3', '2') |
+------------------------------+
|                           12 |

select 3.4 << 23.2;
+-------------------------------+
| bitwise_left_shift(3.4, 23.2) |
+-------------------------------+
|                      25165824 |