SQLでフラグ検索！ビット演算で“状態”をサクッと見つける

こんにちは、さるまりんです。

先週は PHP で“1つの整数に複数のフラグ” を詰める話をしました。(PHPでビット演算フラグ管理！)
今回はその続編として、データベースでどう検索するのかをまとめます。

例えばブログ記事の管理なら、

公開／非公開
画像あり／なし
コメント可否

といった状態を boolean で個別カラムにすると列数が増えてしまいますよね。
これを1つの整数にまとめて管理できればテーブルもスッキリします。
そんなときに役立つのがビット演算によるフラグ検索です。

0. 準備（Dockerでサクッと MySQL + Adminer）

試すための環境です。

文字化け防止のため、起動時と接続時に UTF-8（utf8mb4）を指定しています。

docker-compose.yml

services:
  db:
    image: mysql:8.4
    container_name: bitflags-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: bitflags_demo
      MYSQL_USER: app
      MYSQL_PASSWORD: secret
    ports:
      - "3306:3306"
    volumes:
      - ./init:/docker-entrypoint-initdb.d:ro
    command: >
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_0900_ai_ci
      --init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci'
      --sql-mode=STRICT_ALL_TABLES
  adminer:
    image: adminer:latest
    container_name: bitflags-adminer
    ports:
      - "8080:8080"

初期データも入れたいので下も用意しています。

init/001_schema.sql

-- すべての初期化SQLの先頭で UTF-8 を“接続に”明示する (文字化け防止)
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE bitflags_demo;

DROP TABLE IF EXISTS items;

CREATE TABLE items (
  id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title  VARCHAR(128) NOT NULL,
  flags  INT UNSIGNED  NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- フラグ割り当て: 1(ACTIVE), 2(PRIVATE), 4(HAS_IMAGE), 8(FEATURED), 16(ALLOW_COMMENTS)
INSERT INTO items (title, flags) VALUES
('記事A: 公開 + 画像 + コメント可',                   1 | 4 | 16),
('記事B: 公開 + 特集',                               1 | 8),
('記事C: 非公開 + 画像',                              2 | 4),
('記事D: 公開のみ',                                   1),
('記事E: 画像のみ',                                   4),
('記事F: 特集 + コメント可',                          8 | 16),
('記事G: 公開 + 画像 + 特集 + コメント可',            1 | 4 | 8 | 16),
('記事H: コメント可のみ',                              16),
('記事I: 非公開 + コメント可',                         2 | 16),
('記事J: 何もなし',                                    0);

-- 動作確認
SELECT id, title, flags, BIN(flags) AS flags_bin
FROM items
ORDER BY id;

起動コマンド

dockerで環境を立ち上げます。

docker compose up -d

起動後は

Adminer (管理画面) → [http://localhost:8080]でアクセスできます。
(System: MySQL / Server: db / User: app / Pass: secret / DB: bitflags_demo)
コマンドライン →

mysql -h127.0.0.1 -P3306 -uapp -psecret --default-character-set=utf8mb4 bitflags_demo

で接続できます。

初期データ（AdminerでSQLコマンドSELECT * FROM items;を実行した結果です）

1. ビット割り当て（おさらい）

前回のPHPの記事では以下のようにフラグを定義していました。

1 << 0 = 1 → IS_ACTIVE
1 << 1 = 2 → IS_PRIVATE
1 << 2 = 4 → HAS_IMAGE
1 << 3 = 8 → IS_FEATURED
1 << 4 = 16 → ALLOW_COMMENTS

マスク（mask）は ORで合成：(1 | 4) のように組み立てます。

2. 基本検索パターン（ANY / ALL / NONE）

検索のパターンをまとめます。

2-1. ANY（いずれか立っている = OR 条件）

-- HAS_IMAGE(4) が立っている
SELECT id, title, flags FROM items
WHERE (flags & 4) <> 0;

-- IS_ACTIVE(1) または HAS_IMAGE(4) のどちらか（あるいは両方）
SELECT id, title, flags FROM items
WHERE (flags & (1 | 4)) <> 0;

2-2. ALL（全部立っている = AND 条件）

-- IS_ACTIVE(1) かつ HAS_IMAGE(4) が両方立っている
SELECT id, title, flags FROM items
WHERE (flags & (1 | 4)) = (1 | 4);

2-3. NONE（まったく立っていない = NOT 条件）

-- HAS_IMAGE(4) が立っていない
SELECT id, title, flags FROM items
WHERE (flags & 4) = 0;

まとめると

-- OR（ANY）
(flags & :mask) <> 0

-- AND（ALL）
(flags & :mask) = :mask

-- NOT（NONE）
(flags & :mask) = 0

3. 実行例

上のSQLを実際に実行してみました。

3-1. ANY / OR

SELECT id, title FROM items WHERE (flags & (1 | 4)) <> 0 ORDER BY id;

+----+-----------------------------------------------------+
| id | title                                               |
+----+-----------------------------------------------------+
|  1 | 記事A: 公開 + 画像 + コメント可                     |
|  2 | 記事B: 公開 + 特集                                  |
|  3 | 記事C: 非公開 + 画像                                |
|  4 | 記事D: 公開のみ                                     |
|  5 | 記事E: 画像のみ                                     |
|  7 | 記事G: 公開 + 画像 + 特集 + コメント可              |
+----+-----------------------------------------------------+
(6 rows)

3-2. ALL / AND

SELECT id, title FROM items WHERE (flags & (1 | 4)) = (1 | 4) ORDER BY id;

+----+-----------------------------------------------------+
| id | title                                               |
+----+-----------------------------------------------------+
|  1 | 記事A: 公開 + 画像 + コメント可                     |
|  7 | 記事G: 公開 + 画像 + 特集 + コメント可              |
+----+-----------------------------------------------------+
(2 rows)

3-3. NONE / NOT

SELECT id, title FROM items WHERE (flags & 4) = 0 ORDER BY id;

+----+--------------------------------------+
| id | title                                |
+----+--------------------------------------+
|  2 | 記事B: 公開 + 特集                   |
|  4 | 記事D: 公開のみ                      |
|  6 | 記事F: 特集 + コメント可             |
|  8 | 記事H: コメント可のみ                |
|  9 | 記事I: 非公開 + コメント可           |
| 10 | 記事J: 何もなし                      |
+----+--------------------------------------+
(6 rows)

4. ちょっと解説、Q&Aです

なぜ <> 0 / = mask / = 0？ → flags & mask は 必要ビットだけを抽出するため。1つでも立っていれば 0 ではなく、全部立っていれば mask と一致します。
インデックスは？ → flags & mask は 関数適用になるので素のインデックスは効きにくいです。最適化（生成列・部分正規化など）は次回に掘り下げます。