MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual

12.10.1. Bit Functions

Name	Description
`BIT_AND()`	Return bitwise and
`BIT_COUNT()`	Return the number of bits that are set
`BIT_OR()`	Return bitwise or
`BIT_XOR()`	Return bitwise xor
`&`	Bitwise AND
`\|`	Bitwise OR
`^`	Bitwise XOR
`<<`	Left shift
`>>`	Right shift
`~`	Invert bits

MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

|

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

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

^

Bitwise XOR:

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

The result is an unsigned 64-bit integer.

Bitwise XOR was added in MySQL 4.0.2.

<<

Shifts a longlong (BIGINT) number to the left.
```
mysql> SELECT 1 << 2;
        -> 4
```
The result is an unsigned 64-bit integer.
>>

Shifts a longlong (BIGINT) number to the right.
```
mysql> SELECT 4 >> 2;
        -> 1
```
The result is an unsigned 64-bit integer.
~

Invert all bits.
```
mysql> SELECT 5 & ~1;
        -> 4
```
The result is an unsigned 64-bit integer.
BIT_COUNT(N)

Returns the number of bits that are set in the argument N.
```
mysql> SELECT BIT_COUNT(29);
        -> 4
```

User Comments

Posted by greg gerard on October 25 2005 12:44pm

[Delete] [Edit]

That is an sample of utilisation:

SELECT
SUM(IF(rubrik & 1, 1, 0)) actus,
SUM(IF(rubrik & 2, 1, 0)) shopping,
SUM(IF(rubrik & 4, 1, 0)) utils,
SUM(IF(rubrik & 8, 1, 0)) communication,
SUM(IF(rubrik & 16, 1, 0)) services,
COUNT(user_id) AS total,
FROM preferences p

Where rubrik is an integer.

Add your own comment.