毎度、自ブログからの転載です。
つい最近、自作のWordPressカスタムデータベース管理プラグイン「Custom DataBase Tables」への問い合わせで bit型フィールド についてデフォルト値を指定するとテーブル作成がコケるという不具合報告があって、 MySQLのbit型フィールド について詳しく調べることになった。その結果、色々と新たにわかったことがあったので、その備忘録も兼ねて記事を書いておこうかと。
もともと私は、MySQLのbit型はboolean（bool）型と同様に tinyint(1)のシノニム（フィールド形式のエイリアスみたいなもの） と理解していたので、フィールドの仕様もtinyint(1)と同じだと認識していたんだが、実際は違っていたんだなこれが。
各フィールドの仕様は下記のようになってた（ちなみにMySQLのバージョンは5.6.16）。
| フィールド型 | 表示桁数 | 値範囲 （※ UNSIGNEDなし） |
値範囲 （※ UNSIGNED時） |
CREATE TABLE後のフィールド型 | 備考 |
|---|---|---|---|---|---|
| tinyint | 1~（※1） | -128~127 | 0~255 | tinyint(n) | 数値タイプフィールドに属する |
| boolean | - | -128~127 | -（※2） | tinyint(1) | tinyint(1)のシノニム |
| bool | - | -128~127 | -（※2） | tinyint(1) | tinyint(1)のシノニム、かつbooleanのエイリアス |
| bit | 1~64 | - | - | bit(n) | 数値タイプフィールドに属する。格納される値がバイナリ値になる |
-
※1 : tinyint型フィールドの表示桁数としては通例は
4である。これは、-（マイナス）の符号を含めた最小値が-128と4桁になるからなのかな？ 表示桁が格納可能値より小さく指定しても、格納値が表示幅を超えた場合には指定値は無視され、格納値範囲が変わるわけではないので注意！ ZEROFILL属性付けた時の0埋めする桁数と思っておく程度でいいかも。 - ※2 : CREATE TABLEのSQL文内のカラム定義部には指定できない（指定するとエラーになる）。ZEROFILL属性も同様である。
では、それぞれのフィールド型に色んな値を格納してみる。
| INSERT値→ | 0 | 1 | 127 | 255 | -128 | 65535 | -65535 | true | false | 'true' | 'false' | b'0' | b'1' | b'1111' |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| tinyint(1) | 0 | 1 | 127 | 127 | -128 | 127 | -128 | 1 | 0 | 0 | 0 | 0 | 1 | 15 |
| tinyint(1) UNSIGNED | 0 | 1 | 127 | 255 | 0 | 255 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 15 |
| boolean | 0 | 1 | 127 | 127 | -128 | 127 | -128 | 1 | 0 | 0 | 0 | 0 | 1 | 15 |
| bool | 0 | 1 | 127 | 127 | -128 | 127 | -128 | 1 | 0 | 0 | 0 | 0 | 1 | 15 |
| bit(1) | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 1 | 1 |
| bit(64) (2進数) | 0 | 1 | 1111111 | 11111111 | (※1) | (※2) | (※3) | 1 | 0 | (※4) | (※5) | 0 | 1 | 1111 |
| bit(64) (8進数) | 0 | 1 | 177 | 377 | (※6) | 177777 | (※7) | 1 | 0 | (※8) | (※9) | 0 | 1 | 17 |
| bit(64) (10進数) | 0 | 1 | 127 | 255 | (※10) | 65535 | (※11) | 1 | 0 | (※12) | (※13) | 0 | 1 | 15 |
| bit(64) (16進数) | 0 | 1 | 7F | FF | (※14) | FFFF | (※15) | 1 | 0 | (※16) | (※17) | 0 | 1 | F |
- ※1 :bit(64)（2進数）での
-128=1111111111111111111111111111111111111111111111111111111110000000 - ※2 :bit(64)（2進数）での
65535=1111111111111111 - ※3 :bit(64)（2進数）での
-65535=1111111111111111111111111111111111111111111111110000000000000001 - ※4 :bit(64)（2進数）での
'true'=1110100011100100111010101100101 - ※5 :bit(64)（2進数）での
'false'=110011001100001011011000111001101100101 - ※6 :bit(64)（8進数）での
-128=1777777777777777777600 - ※7 :bit(64)（8進数）での
-65535=1777777777777777600001 - ※8 :bit(64)（8進数）での
'true'=16434472545 - ※9 :bit(64)（8進数）での
'false'=6314133071545 - ※10:bit(64)（10進数）での
-128=18446744073709551488 - ※11:bit(64)（10進数）での
-65535=18446744073709486081 - ※12:bit(64)（10進数）での
'true'=1953658213 - ※13:bit(64)（10進数）での
'false'=439721161573 - ※14:bit(64)（16進数）での
-128=FFFFFFFFFFFFFF80 - ※15:bit(64)（16進数）での
-65535=FFFFFFFFFFFF0001 - ※16:bit(64)（16進数）での
'true'=74727565 - ※17:bit(64)（16進数）での
'false'=66616C7365
tinyint(1)とそのシノニムであるboolean（bool）は純粋な数値型フィールドなので、0や1以外の数値も指定レンジ内であれば取り扱えるし、指定レンジを越えるとそれぞれ最小値・最大値に丸められるというMySQLの数値型フィールドの仕様のままだ。ただ、数値型フィールドながらbit型フィールドは格納される値がバイナリ値（2進数のビット値）になるため、毛色が全く違う。
まず普通に値を取り出そうとしてSELECT文を流すとバイナリ値が返って来てしまうので、値を取得する場合はn進数のビット値に応じて、10進数なら「{column_name}+0」、2進数なら「BIN({column_name})」、8進数なら「OCT({column_name})」、16進数なら「HEX({column_name})」というふうにビット計算関数を仲介させてやる必要がある。例えば、bit型フィールドの「col_bit」カラムの値をすべて10進数と16進数で取得したい場合、
> SELECT col_bit+0,HEX(col_bit) FROM table_name;
のようにしないとならない。bit型フィールドに対して普通にSELECT文流して、しかも値変換せずに直接利用しようとすると文字化けが起きたり、値がNULLになったりするので注意が必要だ（私のプラグイン「Custom DataBase Tables」（バージョン1.1.13）にもこのバグがまだ残っているんだなこれが…（汗））。
まだbit(1)であれば、何進数でもすべて0と1のみで値が格納されているので、わかりやすいんだが、bit(2)以上のレンジになると格納値の実際の値が一目ではわからなくなる。上の表の例で、64ビット型フィールド（bit(64)の格納値）が良い例だ。
しかし、そんな取り扱い注意なbit型フィールドでも、とりわけ「bit(1)」型フィールドはすべての格納値が0と1のどちらかにしかならないという特徴を持っていて、これがboolean型とは名ばかりのtinyint(1)型フィールドと違って、真偽値を格納したいフィールドとしてはまさに最適なフィールドといえるのだ。
なぜなら、bool値（文字列ではダメ！）をそのままINSERTできるので、
> INSERT INTO table_name SET bool_bit=true;
OR
> INSERT INTO table_name SET bool_bit=false;
──と真偽値をダイレクトに格納して、
> SELECT * FROM table_name WHERE bool_bit=true;
OR
> SELECT * FROM table_name WHERE bool_bit=false;
──のように、非常にわかりやすい真偽値判定ですべての値を絞り込めてしまうからだ。
bit(1)型フィールドに「NOT NULL」属性を付けておけば、このフィールドには完全に 1=true か、 0=false のデータしか存在しないことになり、完璧な真偽値判定用フィールドとすることが可能だ。
なお、テーブル作成時のカラム定義で、bit型フィールドに初期値を設定する場合は、 DEFAULT b'{value}' を使わないといけない。 {value} の部分は2進数のバイナリ値だ。
例えば、真偽値判定用にbit(1)型フィールドの初期値を 0=false にしておきたい場合のSQLは、
CREATE TABLE `table_name` (
bool_bit bit(1) NOT NULL DEFAULT b'0'
);
──というふうになるわけだ。
あと、bit(1)型フィールドを真偽値判定用フィールドとして利用する場合に注意しておかないいけないのは、bit(1)型フィールドで 0=false となる値が格納されるケースが、
- 数値の
0 - bool値の
false - バイナリ値の
b'0'
──の３つしかないということだ。それ以外の値を格納するとすべて 1=true となってしまうので、格納する際の値のバリデーションが重要なことは言うまでもない。
さぁて、「Custom DataBase Tables」プラグインのbit型フィールド用のSELECT処理を修正しないとなぁ…。