Skip navigation links

Additional languages
11.10.2 Encryption and Compression Functions »
Section Navigation      [Toggle]

11.10.1. Bit Functions

Table 11.15. Bitwise Functions

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

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. 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.

  • >>

    Shifts a longlong (BIGINT) number to the right. 

    mysql> SELECT 4 >> 2;
        -> 1

    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.

  • ~

    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.

Posted by Neil Davis on April 3 2008 8:07pm[Delete] [Edit]

If you store your ip addresses as a 32 bit unsigned integer representation instead of strings(using INET_ATON()), you can use bitwise operators to pull the octets for grouping and sorting when you need to retrieve them:
select
ipAddress,
(ipAddress >> 24) as firstOctet,
(ipAddress>>16<<16)-(ipAddress>>24<<24)>>16 as secondOctet,
(ipAddress>>8<<8)-(ipAddress>>16<<16)>>8 as thirdOctet,
ipAddress-(ipAddress>>8<<8) as fourthOctet
from ips;
Result:

+------------+------------+-------------+------------+-------------+
| ipAddress  | firstOctet | secondOctet | thirdOctet | fourthOctet |
+------------+------------+-------------+------------+-------------+
| 2082027709 |        124 |          25 |         56 |         189 |
| 2082027710 |        124 |          25 |         56 |         190 |
| 2082027711 |        124 |          25 |         56 |         191 |
| 2082093145 |        124 |          26 |         56 |          89 |
| 2082093146 |        124 |          26 |         56 |          90 |
+------------+------------+-------------+------------+-------------+

This way you don't need a string parser to separate the octets for display, drill down menus, sorting etc, and don't need to tear the octets apart with a string parser to put them in separate fields.

If these are your IP addresses I sincerely apologize. I just pulled them out of thin air to write this sql :D

There are 10 types of people in the world...
Sorry had to throw that in there 8)

There are probably "easier" ways to do this, but they aren't as efficient. I had a requirement for a tree view of ip addresses and this seems to be the easiest way to do it.

-Neil

Posted by Phillip Temple on September 11 2008 1:32pm[Delete] [Edit]

Neil, you will find the following more efficient:
ipAddress, (ipAddress >> 24) as firstOctet,
(ipAddress>>16) & 255 as secondOctet,
(ipAddress>>8) & 255 as thirdOctet,
ipAddress & 255 as fourthOctet
from ips;

Phillip.

Posted by Ben Wiseley on August 7 2009 9:54pm[Delete] [Edit]

This took me a while to figure out so, if it helps anyone...

If you want to turn on/off bit settings you do it like so, you need to do the &~ first and the | second.

Say prefs = 1636 (1024 | 512 | 64 | 32 | 4) and you want to turn off 512 and 64 and turn on 4 and not touch 1024

select prefs &~ 512 &~ 64 | 4;
1060

Even if prefs is 0 - you get the same result


Just make sure to put all the &~s first

Add your own comment.