MariaDBでフラグを管理

MariaDBのビット型を使ってデータを格納をした際の覚書です。

列のタイプとビットの表記

MariaDBでビット処理をしたい場合に、それ用に用意されている列のタイプには「 BIT 」があります。

書くまでもないことですが、ロジック側で対応するするなら、「 INT 」や「 BIGINT 」、「 CHAR 」や「 VARCHAR 」も使えます。

ビットは bの接頭(ビットリテラル)の後に2進数文字列を続けることでコンソール上で利用可能です。

ただ、コンソール上でビット値を表示しようとすると文字化けしますので、通常は数値へキャストします。0と1の文字列にしたい場合は数値からBIN関数を通します。

/* bit型をコンソールに表示しようとすると文字化けします */
SELECT b'101' AS bit ;
+-----+
| bit |
+-----+
|  □ |
+-----+

/* bitの2進数値を10進数の値にして表示します */
SELECT CAST(b'101' AS UNSIGNED) bit;
+-----+
| bit |
+-----+
|   5 |
+-----+

/* 0と1の文字列にしたい場合は10進数の値からBIN関数を使います */
SELECT BIN(CAST(b'101' AS UNSIGNED)) bit;
+-----+
| bit |
+-----+
| 101 |
+-----+

BIT列の作成と挿入

列のタイプを BIT にする際は1～64の値を渡します。筆者の環境では何も設定しない場合や、0を指定した場合はサイズ1となりました。

マイナスの値や、65以上の値をセットするとエラーになります。

CREATE TABLE bit_test1(col1 BIT(32));
CREATE TABLE bit_test2(col1 BIT);

この列にデータを挿入したい場合、10進数の値かもしくはbの接頭を付けたバイナリ値を渡します。

INSERT INTO bit_test1 VALUES(128);
INSERT INTO bit_test1 VALUES(b'11001');

BIT列から行を選択

このようにして作成したBIT列の値を抽出するには論理演算を用います。

その際のいくつかの例示をします。

• 2ビット目に1が立っている行を抽出
  SELECT BIN(CAST(col1 AS UNSIGNED)) bit FROM bit_test1 WHERE (col1 & b'10') = b'10';
• 3ビット目に1が立っていない行を抽出
  SELECT BIN(CAST(col1 AS UNSIGNED)) bit FROM bit_test1 WHERE (col1 & b'100') = 0;
• 1ビット目か3ビット目に1が立っている行を抽出
  SELECT BIN(CAST(col1 AS UNSIGNED)) bit FROM bit_test1 WHERE (col1 & b'101') != 0;

  | 演算子を用いることもできます。

  SELECT BIN(CAST(col1 AS UNSIGNED)) bit FROM bit_test1 WHERE (col1 & (b'1' | b'100')) != 0;
• すべての行から4ビット目を除去
  UPDATE bit_test1 SET col1=col1 & ~b'1000' FROM bit_test1;

  ~は反転を意味して、指定した列のサイズ(ビット)で反転値を生成します。

ビットリテラルを付与していいる部分では、10進数化した値を用いることも可能です。

たとえば「 WHERE (col1 & b'101') 」は「 WHERE (col1 & 5) 」と置き換える事ができます。

INT型で定義された列も論理演算は使用可能

INT型(10進数)で列を定義している場合でもビット演算(論理演算)をすることができます。

たとえば、次のようなテーブルがあるとします。

MariaDBでは値を自動的にキャストしてくれるので、BITで定義した列に10進数の値を挿入できるように、INTで定義した列にBITを挿入しても意図した値が入ります。

たとえば次のようにすると列には5が入ります。

INSERT INTO bit_test3 VALUES(b'101');
SELECT * FROM bit_test3;
+------+
| col1 |
+------+
|    5 |
+------+

条件においても同様で、10進数で書いてもビットリテラルをつけても処理が可能です。

SELECT BIN(col2) bit FROM bit_test3 WHERE (col1 & 2) = 2;

UPDATE bit_test3 SET col1=(col1 & ~b'1');

ビットリテラルがINT型の列に対してもでも意図したように機能するので、使うケースは少ないかもしれませんが、先ほどBIN関数で出力した2進数の文字列を10進数に変換する場合は、conv関数を用います。

SELECT CONV('10001',2,10) AS num;
+------+
| num  |
+------+
| 17   |
+------+

CONV関数は、第1引数に指定した文字列か数値を、第2引数で指定した基数から第3引数に指定した基数に変換します。つまりここでは'10001'という文字列を、2進数から10進数に変換しています。

SELECT文で取得する場合キャストしないといけないことを考えると、通常はビットを利用したい場合でもINTやBIGINTを用いた方が扱いやすいかもしれません。

BITを使うことで得られるメリットは、フラグ値であるという意図の表現や、データ量の削減(INTやBIGINTはひとつの列に対して32または64ビットを消費しますが、BITで定義した場合の領域は指定したサイズとなります)といったところでしょうか。