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 = 1IS_ACTIVE
  • 1 << 1 = 2IS_PRIVATE
  • 1 << 2 = 4HAS_IMAGE
  • 1 << 3 = 8IS_FEATURED
  • 1 << 4 = 16ALLOW_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関数適用になるので素のインデックスは効きにくいです。最適化(生成列・部分正規化など)は次回に掘り下げます。

ビット演算を使うと、1列の整数から“状態”を ANY/ALL/NONE でシンプルに検索できます。

マスクは ORで合成、クエリは上の3パターンが基本です。
まずは基礎をマスターしておくと、どのような用途に強いか、逆に苦手かも見えてきます。

「生成列を使ったインデックス最適化」や「部分正規化との使い分け」については改めて掘り下げていきたいと思います。

読んでくださってありがとうございました。
それではまた!