amari3のはてなダイアリー このページをアンテナに追加 RSSフィード Twitter

2012-01-14

[]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=1FALSE=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 TRUEIS 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値を含む行は返らないことも併せて分かった。

最後に

BOOLEAN型やTRUE/FALSE、IS TRUE/IS FALSEというのがあるというのも調べるまで知らなかったし、MySQLは調べる度に発見があってすごく楽しい。

実際のテーブル設計では、一般的な型や値を使う方が他のRDBMSへの移植性も高くなるし、使わない方がいいんだろうと考えられる。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/amari3/20120114/1326557682