Skip navigation links
**Section Navigation** [Toggle]

MySQL 5.0 Reference Manual :: 12 Functions and Operators :: 12.11 Bit Functions

- 12 Functions and Operators
- 12.1 Function and Operator Reference
- 12.2 Type Conversion in Expression Evaluation
- 12.3 Operators
- 12.4 Control Flow Functions
- 12.5 String Functions
- 12.6 Numeric Functions and Operators
- 12.7 Date and Time Functions
- 12.8 What Calendar Is Used By MySQL?
- 12.9 Full-Text Search Functions
- 12.10 Cast Functions and Operators
- 12.11 Bit Functions
- 12.12 Encryption and Compression Functions
- 12.13 Information Functions
- 12.14 Spatial Analysis Functions
- 12.15 Miscellaneous Functions
- 12.16 Functions and Modifiers for Use with GROUP BY Clauses
- 12.17 Precision Math

MySQL uses `BIGINT`

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

Bitwise OR:

mysql>

-> 31`SELECT 29 | 15;`

The result is an unsigned 64-bit integer.

Bitwise AND:

mysql>

-> 13`SELECT 29 & 15;`

The result is an unsigned 64-bit integer.

Bitwise XOR:

mysql>

-> 0 mysql>`SELECT 1 ^ 1;`

-> 1 mysql>`SELECT 1 ^ 0;`

-> 8`SELECT 11 ^ 3;`

The result is an unsigned 64-bit integer.

Shifts a longlong (

`BIGINT`

) number to the left.mysql>

-> 4`SELECT 1 << 2;`

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>

-> 1`SELECT 4 >> 2;`

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>

-> 4`SELECT 5 & ~1;`

The result is an unsigned 64-bit integer.

Returns the number of bits that are set in the argument

.`N`

mysql>

-> 4, 3`SELECT BIT_COUNT(29), BIT_COUNT(b'101010');`

## User Comments

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.

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:

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

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.

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

@ 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

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

@Mark Cain.

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