MySQLにおけるBOOLEAN型の扱いについて調べてみた
PostgreSQLで定義されているテーブルからMySQLに移植する必要があって、作業をしてるとBOOLEAN型で定義されてるカラムが見つかった。単純にTINYINTとかで置き換えてもよかったんだけど、MySQLにもあるんだろうかと興味本位で調べてみた。
MySQLのBOOLEAN型はTINYINT(1)と等価
以下のようなテーブル定義をしてみた。
CREATE TABLE IF NOT EXISTS bool_test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
flg BOOLEAN,
updated_at TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8;作成されたテーブルはこちら。
mysql> desc bool_test; +------------+------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+-------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | flg | tinyint(1) | YES | | NULL | | | updated_at | timestamp | NO | | CURRENT_TIMESTAMP | | +------------+------------------+------+-----+-------------------+----------------+ 3 rows in set (0.00 sec)
BOOLEAN型がTINYINT(1)と等価なのが分かると思う。つまり、真偽値だけを格納できる訳ではない。
実際に値を入れてみる
以下のテストデータを入れてみた。
INSERT INTO bool_test (flg) VALUES (TRUE), (FALSE), (1), (0), (100), (-100), (NULL), (TRUE), (FALSE);
作成されたデータはこちら。
mysql> SELECT * FROM bool_test ORDER BY id; +----+------+---------------------+ | id | flg | updated_at | +----+------+---------------------+ | 1 | 1 | 2012-01-15 00:35:08 | | 2 | 0 | 2012-01-15 00:35:08 | | 3 | 1 | 2012-01-15 00:35:08 | | 4 | 0 | 2012-01-15 00:35:08 | | 5 | 100 | 2012-01-15 00:35:08 | | 6 | -100 | 2012-01-15 00:35:08 | | 7 | NULL | 2012-01-15 00:35:08 | | 8 | 1 | 2012-01-15 00:35:08 | | 9 | 0 | 2012-01-15 00:35:08 | +----+------+---------------------+ 9 rows in set (0.00 sec)
TRUE=1、FALSE=0にそれぞれ変換されて格納されているのが分かる。あと、TINYINT型なんで当然だけど、-128〜127までの整数値なら入れる事が出来る。
BOOLEAN型のカラムをSELECTしてみる
ここまでで、テーブルの定義結果とデータの格納結果が分かったので、flgカラムを色々なやり方でSELECTしてみた。
TRUE/FALSEで比較してSELECT
まずは一番単純にWHERE句条件にTRUE/FALSEを指定してみる。
mysql> SELECT * FROM bool_test WHERE flg = TRUE ORDER BY id; +----+------+---------------------+ | id | flg | updated_at | +----+------+---------------------+ | 1 | 1 | 2012-01-15 00:35:08 | | 3 | 1 | 2012-01-15 00:35:08 | | 8 | 1 | 2012-01-15 00:35:08 | +----+------+---------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM bool_test WHERE flg = FALSE ORDER BY id; +----+------+---------------------+ | id | flg | updated_at | +----+------+---------------------+ | 2 | 0 | 2012-01-15 00:35:08 | | 4 | 0 | 2012-01-15 00:35:08 | | 9 | 0 | 2012-01-15 00:35:08 | +----+------+---------------------+ 3 rows in set (0.00 sec)
実行結果を見ると、TRUE=1、FALSE=0として評価されているのが分かると思う。
IS TRUE/IS FALSEで比較してSELECT
色々調べてみるとIS TRUE、IS FALSEという比較方法があることが分かったので試してみた。
mysql> SELECT * FROM bool_test WHERE flg IS TRUE ORDER BY id; +----+------+---------------------+ | id | flg | updated_at | +----+------+---------------------+ | 1 | 1 | 2012-01-15 00:35:08 | | 3 | 1 | 2012-01-15 00:35:08 | | 5 | 100 | 2012-01-15 00:35:08 | | 6 | -100 | 2012-01-15 00:35:08 | | 8 | 1 | 2012-01-15 00:35:08 | +----+------+---------------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM bool_test WHERE flg IS FALSE ORDER BY id; +----+------+---------------------+ | id | flg | updated_at | +----+------+---------------------+ | 2 | 0 | 2012-01-15 00:35:08 | | 4 | 0 | 2012-01-15 00:35:08 | | 9 | 0 | 2012-01-15 00:35:08 | +----+------+---------------------+ 3 rows in set (0.01 sec)
実行結果を見ると、IS TRUEはflg=0以外の行を返してくる。つまり、TRUEという値とIS TRUEは直接の関係は無さそうである。そして、IS FALSEはflg=0の行を返してくる。IS FALSEではNULL値を含む行は返らないことも併せて分かった。
