Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


12.12 ビット関数

表 12.16 ビット単位の関数

名前 説明
BIT_COUNT() 設定されているビット数を返します
& ビット単位の AND
~ ビットを反転します
| ビット単位の OR
^ ビット単位の XOR
<< 左シフト
>> 右シフト

MySQL では、演算子の最大範囲が 64 ビットになるように、ビット演算に BIGINT (64 ビット) 算術が使用されます。

  • |

    ビット単位の OR:

    mysql> SELECT 29 | 15;
            -> 31
    

    結果は符合なしの 64 ビット整数です。

  • &

    ビット単位の AND:

    mysql> SELECT 29 & 15;
            -> 13
    

    結果は符合なしの 64 ビット整数です。

  • ^

    ビット単位の XOR:

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

    結果は符合なしの 64 ビット整数です。

  • <<

    longlong (BIGINT) 数値を左にシフトします。

    mysql> SELECT 1 << 2;
            -> 4
    

    結果は符合なしの 64 ビット整数です。値は 64 ビットに切り捨てられます。特に、シフト数が符号なし 64 ビット数値の幅以上の大きさの場合は、結果がゼロになります。

  • >>

    longlong (BIGINT) 数値を右にシフトします。

    mysql> SELECT 4 >> 2;
            -> 1
    

    結果は符合なしの 64 ビット整数です。値は 64 ビットに切り捨てられます。特に、シフト数が符号なし 64 ビット数値の幅以上の大きさの場合は、結果がゼロになります。

  • ~

    すべてのビットを反転します。

    mysql> SELECT 5 & ~1;
            -> 4
    

    結果は符合なしの 64 ビット整数です。

  • BIT_COUNT(N)

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