SQLでビット演算

※MySQLを前提としています

// 結局実務に使うことはなく中途半端。気が向いたら加筆します

例えば「販売商品のカテゴリを複数設定可能にしたい」という場合。

• みかん（生鮮/果物）
• 冷凍ミカン（冷凍/果物）
• みかん缶詰（缶詰/果物）
• いわし缶詰（缶詰/魚）
• 冷凍マグロ（生鮮/冷凍/魚）

…みたいなね。

普段は以下のようにテーブル分けしています。

• 商品テーブル
  • 商品ID(PK)
  • 商品名
• カテゴリテーブル
  • カテゴリID(PK)
  • カテゴリ名
• 商品×カテゴリテーブル
  • 商品ID(PK)
  • カテゴリID(PK)

カテゴリがかなり重要な意味を持つ場合はこれでよいと思うのですが、
ちょっとした区分をくっつけたいだけの場合に大げさすぎる感じがしたので、
ビット演算で対応できないか試してみました。

結論としては、商品テーブルにカテゴリをくっつけるだけでいけます。

• 商品テーブル
  • 商品ID
  • 商品名
  • カテゴリ

このカテゴリには、例えば2進数で「11000」のような値が入ります。
2進数で左の桁から順に「生鮮」「果物」「魚」「缶詰」「冷凍」のように意味を持たせ、
1の部分が有効としています。
つまり「11000」なら「生鮮」「果物」のカテゴリというわけです。
10進数に変換した値を入れてもよいのですが、
桁数などで問題がなければ、パッと見てわかりやすい2進数でよいのではないかと思います。

もう少し単純化して具体例を。
3タイプあり、右の桁からA・B・Cというカテゴリが割り当てられているものとします。

type	補足
000	（いずれも該当しない）
001	A
010	B
011	AとB
100	C
101	AとC
110	BとC
111	AとBとC

こういうtypeデータを持つテーブルに対して、
例えばカテゴリAを含むレコードを取得したい場合は「001」で論理積をとります。

どうせなら全パターン書きますか。

SELECT ... FROM ... WHERE `type` & 000; -- 何もヒットしない
SELECT ... FROM ... WHERE `type` & 001; -- Aを含むレコード(001,011,101,111)がヒット
SELECT ... FROM ... WHERE `type` & 010; -- Bを含むレコード(010,011,110,111)がヒット
SELECT ... FROM ... WHERE `type` & 011; -- AかBを含むレコード(001,010,011,101,110,111)がヒット
SELECT ... FROM ... WHERE `type` & 100; -- Cを含むレコード(100,101,110,111)がヒット
SELECT ... FROM ... WHERE `type` & 101; -- AかCを含むレコード(001,011,100,101,110,111)がヒット
SELECT ... FROM ... WHERE `type` & 110; -- BかCを含むレコード(010,011,100,101,110,111)がヒット
SELECT ... FROM ... WHERE `type` & 111; -- AかBかCを含むレコード(000以外)がヒット

先述の通り分かりやすくするために2進数としていますが、10進数を使うこともできます。

SELECT ... FROM ... WHERE `type` & 0; -- 000と同じ
SELECT ... FROM ... WHERE `type` & 1; -- 001と同じ
SELECT ... FROM ... WHERE `type` & 2; -- 010と同じ
...

また、もちろん単純に一致を取ることもできます。

SELECT ... FROM ... WHERE `type` = 011; -- AとBのみを含むレコード(011)がヒット