MySQLにてBOOLEAN型のフィールドを作りたいと思ったのですが、Googleで調べると「MySQLのBOOLEAN型は実質tinyint(1)と同じ」といったことが書いてありました。 また、「MySQLで厳密なBOOLEAN型を表現したければ、ENUM(‘TRUE’, ‘FALSE’) を使った方がいい」とも書いてありました。
個人的にはSQL文に「WHERE flag IS TRUE」と書いてSELECTできれば文句ありません。そういった挙動ができるのか確認しました。
実験した環境のMySQLバージョンは5.1.41です。
データはどのように格納されるのか？
以下のように、連番とブール値を対に持つテーブルを作りました。
CREATE TABLE boolean_test (
`id` INT NOT NULL AUTO_INCREMENT,
`flag` BOOLEAN,
PRIMARY KEY (`id`)
);
テストデータは下記の通りです。ブーリアン（TRUE/FALSE）のほか、整数値やNULLでも実験します。
INSERT INTO boolean_test VALUES (1, TRUE); INSERT INTO boolean_test VALUES (2, FALSE); INSERT INTO boolean_test VALUES (3, 1); INSERT INTO boolean_test VALUES (4, 0); INSERT INTO boolean_test VALUES (5, NULL); INSERT INTO boolean_test VALUES (6, 127); INSERT INTO boolean_test VALUES (7, 128); INSERT INTO boolean_test VALUES (8, 2147483647); -- 4 byteの最大値 INSERT INTO boolean_test VALUES (9, 2147483648); -- 4 byteの最大値+1
作成結果は下のようになりました。
mysql> SELECT * FROM boolean_test; +----+------+ | id | flag | +----+------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 0 | | 5 | NULL | | 6 | 127 | | 7 | 127 | | 8 | 127 | | 9 | 127 | +----+------+ 9 rows in set (0.00 sec)
この時点で、TRUEは1として、FALSEは0として格納されています。 また、どんなに大きな値を入れても127で止まっています。 確かにMySQL内部でtinyint(1)として管理しているのがわかります。
実際、テーブルをDESCRIBEしてると、型はtinyint(1)と表示されます。
mysql> DESCRIBE boolean_test; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | flag | tinyint(1) | YES | | NULL | | +-------+------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
BOOLEAN型としてSELECTできるか？
条件を「=」で指定してみる
SELECT * FROM boolean_test WHERE flag = TRUE; -- id:1, 3 がヒット SELECT * FROM boolean_test WHERE flag = FALSE; -- id:2, 4 がヒット SELECT * FROM boolean_test WHERE flag = 1; -- id:1, 3 がヒット SELECT * FROM boolean_test WHERE flag = 0; -- id:2, 4 がヒット
「TRUE = 1」「FALSE = 0」と扱っているようです。その証拠に、値が127のレコードは「flag = TRUE」でもヒットしません。
→「flag = TRUE」という条件は、「flag = 1」と同値。
条件を「IS」で指定してみる
SELECT * FROM boolean_test WHERE flag IS TRUE; -- id:1, 3, 6, 7, 8, 9 がヒット SELECT * FROM boolean_test WHERE flag IS FALSE; -- id:2, 4 がヒット
0より大きいものはTRUEとして判別される模様。
→「flag = TRUE」と「flag IS TRUE」は全く別の結果を返す。
UNKNOWNはどういう扱いになる？
SQL-99の定義によると、「BOOLEAN型はTRUE/FALSE/UNKNOWNの3値で表現される」ようです。 MySQLではどういう風に呼び出せるのか確かめてみました。
SELECT * FROM boolean_test WHERE flag IS UNKNOWN; -- id: 5 がヒット SELECT * FROM boolean_test WHERE flag = UNKNOWN; -- NG: #1054 - Unknown column 'UNKNOWN' in 'where clause' SELECT * FROM boolean_test WHERE flag = 'UNKNOWN'; -- ???: id:2, 4 がヒット
NULLのフィールドがUNKNOWNとして表現されているようです。 また、「= UNKNOWN」と指定するとクエリが失敗しました。「IS UNKNOWN」が正しいようです。
よくわからないのが3つめの例。id:2, 4はFALSEのレコードなので、全くヒットしてほしくないのですが。 このあたりはMySQLの仕様をよく確認しないとわかりませんが、まず入力しないだろうということで、この際無視します。
考察
やはりMySQLのBOOLEAN型はtinyint(1)と同じようです。 Boolean型と思ってクエリを投げると意図しない結果が返ってきてしまう危険性があるので、注意が必要です。
また、ENUM型は検証していませんが、SQL標準（SQL99）ではサポートされていないため、プログラムの互換性を考えると積極的に使う気が起きません。
MySQLでBoolean型を表したければ、少し気持ち悪いですがtinyint(1)で表現した方が良いようです。
最近のコメント