ビットフラグをDBのテーブル設計に用いてみる

DBのテーブル設計を行うときにフラグを持つフィールドは、それぞれtinyint(1)とかでフィールドを作って0 or 1を入れるようにしていました。

CREATE TABLE `sample_old` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `flg1` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'フラグ1',
    `flg2` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'フラグ2',
    `flg3` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'フラグ3',
    `flg4` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'フラグ4',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

これだとテーブルに持つフラグが増えた分だけフィールドの数も増えますし、検索SQLのWHERE条件も増えてしまうのどうしようって思っていたら、ビットフラグ使えばいいよねって話になったので試してみます。

動作確認環境:MySQL5.6

ビットフラグとは

2進数とビット演算を用いたフラグ管理方法です。上記sample_oldテーブルのフラグは4つのフラグを持っていますので、4桁の2進数で表現することが出来ます。

2進数 フラグ位置 意味 10進数
0001 1 フラグ1 1
0010 2 フラグ2 2
0100 3 フラグ3 4
1000 4 フラグ4 8

この様にそれぞれのビット(1 or 0)の位置(2進数の桁)に応じた箇所にフラグの意味をもたせるのがビットフラグです。フラグ1とフラグ3が立っている状態はそれぞれのフラグの和で表現できます。

2進数 フラグ位置 意味 10進数
0101 1/3 フラグ1・フラグ3 5

ということは4つのフラグを扱うにはそれぞれのフラグの総和(10進数で15)を扱えればいいのでtinyint(2)のひとつのフィールドで十分ということになります。

ビットフラグ用のテーブルを作成します。

CREATE TABLE `sample_new` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `flg` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'フラグ',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

サンプルデータも入れておきます。

INSERT INTO `sample_new` (flg) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
mysql> SELECT * FROM `sample_new`;
+----+-----+
| id | flg |
+----+-----+
|  1 |   0 |
|  2 |   1 |
|  3 |   2 |
|  4 |   3 |
|  5 |   4 |
|  6 |   5 |
|  7 |   6 |
|  8 |   7 |
|  9 |   8 |
| 10 |   9 |
| 11 |  10 |
| 12 |  11 |
| 13 |  12 |
| 14 |  13 |
| 15 |  14 |
| 16 |  15 |
+----+-----+
16 rows in set (0.00 sec)

フラグの検索・更新を行うにはビット演算を用います。

ビット演算 AND

ANDは2つの値のそれぞれの桁のビットの値を比較し、両方が1であれば1を出力します。

3 AND 1 = 1

これは具体的には以下の様になります。

    0011 | 3
AND 0001 | 1
------------
  = 0001 | 1

SQLではビット演算ANDは&演算子を使います。

mysql> SELECT 3 & 1;
+-------+
| 3 & 1 |
+-------+
|     1 |
+-------+
1 row in set (0.03 sec)

これを利用して特定のフラグ位置の値が1になっているかどうか検索するのに利用できます。 フラグ1が1になっているものを検索するには以下の様なSQLになります。

mysql> SELECT * FROM `sample_new` WHERE flg & 1 != 0;
+----+-----+
| id | flg |
+----+-----+
|  2 |   1 |
|  4 |   3 |
|  6 |   5 |
|  8 |   7 |
| 10 |   9 |
| 12 |  11 |
| 14 |  13 |
| 16 |  15 |
+----+-----+
8 rows in set (0.07 sec)

複数のフラグのいずれかが1になっているものを検索するには、&演算子を使って結果が0でないものを検索します。

mysql> SELECT * FROM `sample_new` WHERE flg & 5 != 0;
+----+-----+
| id | flg |
+----+-----+
|  2 |   1 |
|  4 |   3 |
|  5 |   4 |
|  6 |   5 |
|  7 |   6 |
|  8 |   7 |
| 10 |   9 |
| 12 |  11 |
| 13 |  12 |
| 14 |  13 |
| 15 |  14 |
| 16 |  15 |
+----+-----+
12 rows in set (0.00 sec)

複数のフラグすべてが1になっているものを検索するには、&演算子の結果が、比較した数字と同じになるものを検索します。

mysql> SELECT * FROM `sample_new` WHERE flg & 5 = 5;
+----+-----+
| id | flg |
+----+-----+
|  6 |   5 |
|  8 |   7 |
| 14 |  13 |
| 16 |  15 |
+----+-----+
4 rows in set (0.01 sec)

ビット演算 OR

ORは2つの値のそれぞれの桁のビットの値を比較し、いずれか一方が1であれば1を出力します。

4 OR 1 = 5

これは具体的には以下の様になります。

    0100 | 4
OR  0001 | 1
------------
  = 0101 | 5

SQLではビット演算ORは|演算子を使います。

mysql> SELECT 1 | 4;
+-------+
| 1 | 4 |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

これを利用して、特定のフラグ位置を1に更新することが出来ます。

mysql> UPDATE `sample_new` SET flg = flg | 1;
Query OK, 8 rows affected (0.04 sec)
Rows matched: 16  Changed: 8  Warnings: 0

更新後のデータは以下のようになります。

mysql> SELECT * FROM `sample_new`;
+----+-----+
| id | flg |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   3 |
|  4 |   3 |
|  5 |   5 |
|  6 |   5 |
|  7 |   7 |
|  8 |   7 |
|  9 |   9 |
| 10 |   9 |
| 11 |  11 |
| 12 |  11 |
| 13 |  13 |
| 14 |  13 |
| 15 |  15 |
| 16 |  15 |
+----+-----+
16 rows in set (0.00 sec)

ビット演算 NOT

NOTは各ビットの値を反転します。

NOT 1 = 14

これは具体的には以下の様になります。

NOT 0001 | 1
------------
  = 1110 | 14

SQLではビット演算NOTは~演算子を使います。

mysql> SELECT ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.01 sec)

※ MySQLはBIGINTで計算されるのでこんな事になっていまいましたが(BIGINTは64ビットなので64個のビットが反転したわけです)。

NOT演算子とAND演算子を利用して特定のフラグ位置を0に更新することが出来ます。

mysql> UPDATE `sample_new` SET flg = flg & ~2;
Query OK, 8 rows affected (0.08 sec)
Rows matched: 16  Changed: 8  Warnings: 0

更新後のデータは以下のようになります。

mysql> SELECT * FROM `sample_new`;
+----+-----+
| id | flg |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   1 |
|  5 |   5 |
|  6 |   5 |
|  7 |   5 |
|  8 |   5 |
|  9 |   9 |
| 10 |   9 |
| 11 |   9 |
| 12 |   9 |
| 13 |  13 |
| 14 |  13 |
| 15 |  13 |
| 16 |  13 |
+----+-----+
16 rows in set (0.00 sec)

また特定のフラグ位置が0になっているものを検索することが出来ます。

mysql> SELECT * FROM `sample_new` WHERE ~flg & 4 != 0;
+----+-----+
| id | flg |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   1 |
|  9 |   9 |
| 10 |   9 |
| 11 |   9 |
| 12 |   9 |
+----+-----+
8 rows in set (0.04 sec)

AND検索と同様に複数のフラグのいずれかが0になっているものを検索するには結果が0でないものを検索し、すべてが1になっているものを検索するには結果が比較した数字と同じになるものを検索します。

mysql> SELECT * FROM `sample_new` WHERE ~flg & 12 != 0;
+----+-----+
| id | flg |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   1 |
|  5 |   5 |
|  6 |   5 |
|  7 |   5 |
|  8 |   5 |
|  9 |   9 |
| 10 |   9 |
| 11 |   9 |
| 12 |   9 |
+----+-----+
12 rows in set (0.00 sec)
mysql> SELECT * FROM `sample_new` WHERE ~flg & 12 = 12;
+----+-----+
| id | flg |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   1 |
+----+-----+
4 rows in set (0.00 sec)

ビット演算 XOR

ORは2つの値のそれぞれの桁のビットの値を比較し、それぞれのビットの値が違うものであれば1を出力します。

1 XOR 3 = 2

これは具体的には以下の様になります。

    0001 | 1
NOT 0011 | 3
------------
  = 0010 | 2

SQLではビット演算XORは^演算子を使います。

mysql> SELECT 1 ^ 3;
+-------+
| 1 ^ 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

これを利用して、特定のフラグの値を変更(1→0 or 0→1)することが出来ます。

mysql> UPDATE `sample_new` SET flg = flg ^ 10;
Query OK, 16 rows affected (0.01 sec)
Rows matched: 16  Changed: 16  Warnings: 0

更新後のデータは以下のようになります。

mysql> SELECT * FROM `sample_new`;
+----+-----+
| id | flg |
+----+-----+
|  1 |  11 |
|  2 |  11 |
|  3 |  11 |
|  4 |  11 |
|  5 |  15 |
|  6 |  15 |
|  7 |  15 |
|  8 |  15 |
|  9 |   3 |
| 10 |   3 |
| 11 |   3 |
| 12 |   3 |
| 13 |   7 |
| 14 |   7 |
| 15 |   7 |
| 16 |   7 |
+----+-----+
16 rows in set (0.00 sec)

また、同じ値を利用することでフラグの内容を初期化することが出来ます。

mysql> UPDATE `sample_new` SET flg = flg ^ flg;
Query OK, 16 rows affected (0.03 sec)
Rows matched: 16  Changed: 16  Warnings: 0

更新後のデータは以下のようになります。

mysql> SELECT * FROM `sample_new`;
+----+-----+
| id | flg |
+----+-----+
|  1 |   0 |
|  2 |   0 |
|  3 |   0 |
|  4 |   0 |
|  5 |   0 |
|  6 |   0 |
|  7 |   0 |
|  8 |   0 |
|  9 |   0 |
| 10 |   0 |
| 11 |   0 |
| 12 |   0 |
| 13 |   0 |
| 14 |   0 |
| 15 |   0 |
| 16 |   0 |
+----+-----+
16 rows in set (0.00 sec)

まあ、初期化自体は下記SQLでもいいんですけどね。

UPDATE `sample_new` SET flg = 0;

まとめ

各フラグにそれぞれフィールドを用意した場合に比べて、ビットフラグのフィールドを用意したほうが容量削減・SQL簡素化が期待できます。また、各フラグにそれぞれフィールドを用意した場合はフラグが増えた際にカラム追加作業が発生してしまいますが、ビットフラグの場合だとカラム追加する必要はありません(あったとしても型変更くらい)。

あと、フラグ位置から10進数の値を割り出すには、

2の(フラグ位置 – 1)乗

とすればいいです。SQLではPOWER関数とか使えば求められます。

mysql> SELECT POWER(2, 4-1);
+---------------+
| POWER(2, 4-1) |
+---------------+
|             8 |
+---------------+
1 row in set (0.01 sec)

各データ型が扱えるビット数は以下の通り。

  • TINYINT・・・8bit
  • SMALLINT・・・16bit
  • MEDIUMINT・・・24bit
  • INT・・・32bit
  • BIGINT・・・64bit

コメントを残す