12.13 Bit Functions and Operators

MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

12.13 Bit Functions and Operators

Table 12.17 Bit Functions and Operators

Name	Description
`&`	Bitwise AND
`>>`	Right shift
`<<`	Left shift
`^`	Bitwise XOR
`BIT_COUNT()`	Return the number of bits that are set
`\|`	Bitwise OR
`~`	Bitwise inversion

Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>. (BIT_AND(), BIT_OR(), and BIT_XOR() are aggregate functions described at Section 12.19.1, “Aggregate Function Descriptions”.) Bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types (such as the BINARY and VARBINARY binary string types) are converted to BIGINT and truncation might occur.

The following list describes available bit functions and operators:

|
Bitwise OR.
The result is an unsigned 64-bit integer.
```
mysql> SELECT 29 | 15;
        -> 31
```
&
Bitwise AND.
The result is an unsigned 64-bit integer.
```
mysql> SELECT 29 & 15;
        -> 13
```

Bitwise XOR.

The result is an unsigned 64-bit integer.

mysql> SELECT 1 ^ 1;
        -> 0
mysql> SELECT 1 ^ 0;
        -> 1
mysql> SELECT 11 ^ 3;
        -> 8

<<
Shifts a longlong (BIGINT) number to the left.
The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.
```
mysql> SELECT 1 << 2;
        -> 4
```
>>
Shifts a longlong (BIGINT) number to the right.
The result is an unsigned 64-bit integer. The value is truncated to 64 bits. In particular, if the shift count is greater or equal to the width of an unsigned 64-bit number, the result is zero.
```
mysql> SELECT 4 >> 2;
        -> 1
```
~
Invert all bits.
The result is an unsigned 64-bit integer.
```
mysql> SELECT 5 & ~1;
        -> 4
```
BIT_COUNT(N)
Returns the number of bits that are set in the argument N as an unsigned 64-bit integer, or NULL if the argument is NULL.
```
mysql> SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
        -> 4, 3
```