Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.7Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.2Kb
Man Pages (Zip) - 311.7Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  Functions and Operators  /  Bit Functions and Operators

12.12 Bit Functions and Operators

Table 12.16 Bit Functions and Operators

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

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, “GROUP BY (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:

    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.

  • <<

    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), BIT_COUNT(b'101010');
            -> 4, 3
    

User Comments
  Posted by greg gerard on October 25, 2005
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
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
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
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

  Posted by Will Entriken on December 11, 2009
@ greg gerard

In your case, could use SIGN() rather than IF. Or given that you are using SUM(), even could use >>:

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

  Posted by Mark Cain on December 29, 2009
Seems there is a shortage of examples for bitwise logic in MySQL. Here is how I have used it:

I have a database of customers who buy services from a company. The status of the customer tells me what service(s) they use and if they are current in buying that service. So for an example lets say my services include:
Pool Service, lawn mowing, window washing, tree trimming, house painting, mobile oil change, etc.. (you get the idea).

I set a number scheme for the status such as:
1 = Prospective Customer
2 = Cancelled Service
4 = Pool Service
8 = lawn mowing
16 = window washing
32 = tree trimming
64 = house painting
128 = mobile oil change

With this scheme a customer who needs pool service (4) and window washing (16) will have a status of 20 (4 + 16). A prospect (1) who might be interested in a mobile oil change (128) would have a status of 129 (1+128). etc...

Bitwise logic allows me to select customers who buy particular services based on their status value.

SELECT * FROM customers WHERE status & 1
//returns all prospects)

SELECT * FROM customers WHERE status & 16
//returns all of my window washing customers even if they are cancelled

SELECT * FROM customers WHERE (status & 16) AND !(status & 2)
//returns all of my window washing customers but not the cancelled ones

SELECT * FROM customers WHERE status & 36
//returns all of my tree trimming AND pool service customers i.e. 32+4

SELECT * FROM customers WHERE status & 10
//returns all cancelled lawn moving customers i.e. 2+8

SELECT * FROM customers WHERE (status & 32) AND !(status & 128)
//returns all tree trimming customers who do not get a mobile oil change

  Posted by Chris Wagner on May 28, 2011
@Mark Cain.

What u just described is a reroll of FIND_IN_SET and ENUM.

Sign Up Login You must be logged in to post a comment.