たごもりすメモ

コードとかその他の話とか。

MySQLにおいて真とされる値を調べた

このエントリはMySQL Casual Advent Calendar 2013の1日目の記事です。

はじめに

SQLを書くとき、普通は where でその処理が対象とする行を絞り込みます。要するに where 以下が真である行が対象になるわけですね。簡単には以下のように書きます。

DELETE FROM tbl WHERE 1=1

さて、もちろん WHERE 以下には実際の各行のデータを用いるわけですが、例えば削除用フラグを各行に持たせておいて、バッチ処理で一気に消したい場合ならこのようにします。

-- delflag TINYINT(1)
DELETE FROM tbl WHERE delflag=1

ここで、おっと、と思う人もいると思います。そう、MySQL にはBOOL型がありません。いや、正確にはBOOLという独立の型は存在せず、BOOLといえばTINYINT(1)のことである、となっています*1。また TRUE や FALSE はそれぞれ 1 と 0 とされています。
なので、先程のクエリは正確には以下のように書くこともできます。

-- delflag BOOL
DELETE FROM tbl WHERE delflag

さて、このクエリは好ましいでしょうか。個人的には、これはMySQLではあまり書かないほうがよいクエリだと思います。delflagカラムは真偽を格納するフィールド(BOOL)として宣言されていますが、実際に作成されたテーブルにおける型は TINYINT(1) です。そしてそこには TRUE と FALSE 以外の値を入れることもできます。そしてTRUE/FALSE以外の値がどう解釈されるかはあまり明確でない場合があるからです。

もちろんただのフラグとして扱うなら、理想としては後者のクエリの方が正しいでしょう。しかし現実にどのような値が投入されどう解釈されるかをできるだけ明快にするほうが実運用上は良いかなあ、というのが自分の意見です。

で、ここまでは前置き。「TRUE/FALSE以外の値がどう解釈されるかはあまり明確でない場合」が本題です。

試してみた

このようなクエリを用意して、MySQLで流します。

CREATE TABLE testdata (
`id` INT PRIMARY KEY NOT NULL auto_increment,
`flag_bool` BOOL,
`num_b` TINYINT(1),
`num_int` INT,
`num_dec` DECIMAL(1),
`str` VARCHAR(16),
`ts` TIMESTAMP
) Engine=InnoDB charset='utf8';

INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (FALSE,0,0,0,'','0000-00-00 00:00:00');
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (FALSE,0,0,0,'0','1970-00-00 00:00:00');
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (TRUE,1,1,1,'1','1970-01-01 00:00:01');
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (TRUE,-1,-1,-1,'-1','1970-01-01 09:00:00');
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (2,2,2,2,'2','1970-01-01 09:00:01');
INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (16,16,16,16,'11111',NOW());

そうするとデータの内容はこうなってますね。

mysql> select * from testdata;              
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  1 |      NULL |  NULL |    NULL |    NULL | NULL  | 2013-11-29 16:53:17 | 
|  2 |         0 |     0 |       0 |       0 |       | 0000-00-00 00:00:00 | 
|  3 |         0 |     0 |       0 |       0 | 0     | 0000-00-00 00:00:00 | 
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
7 rows in set (0.01 sec)

これを見ると flag_bool は実際には unsigned になっていることがわかりますね。

さて、ではこのテーブルに順に以下のようなクエリを流して結果を見てみましょう。

SELECT * FROM testdata WHERE col -- colをいろいろ変えてみるよ!

なお最初に片付けておくと、NULL は NULL であり真ではないので、確実に除外されるはずです。気をつけておきたいのは TIMESTAMP だけNULLをINSERTしたのに現在日時が入っているということですね*2。また NOT で反転させても NULL は NULL です。怖いですね。ぬるぬる。

で、期待する結果はこんな感じでしょうか。

  1. 数値型でゼロになっているものは偽、それ以外は真?
    • flag_bool, num_b, num_int, num_dec はそれでよいはず
  2. 文字列の strは?
    • 空文字列が偽? それ以外の条件がある?
  3. タイムスタンプ型 ts は?
    • '0000-00-00 00:00:00' は偽かな?

では実験してみましょう。

mysql> select * from flag_bool;
ERROR 1146 (42S02): Table 'btest.flag_bool' doesn't exist
mysql> select * from testdata where flag_bool;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from testdata where num_b;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from testdata where num_int;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from testdata where num_dec;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from testdata where str;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  4 |         1 |     1 |       1 |       1 | 1     | 0000-00-00 00:00:00 | 
|  5 |         1 |    -1 |      -1 |      -1 | -1    | 0000-00-00 00:00:00 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from testdata where ts;
+----+-----------+-------+---------+---------+-------+---------------------+
| id | flag_bool | num_b | num_int | num_dec | str   | ts                  |
+----+-----------+-------+---------+---------+-------+---------------------+
|  1 |      NULL |  NULL |    NULL |    NULL | NULL  | 2013-11-29 16:53:17 | 
|  6 |         2 |     2 |       2 |       2 | 2     | 1970-01-01 09:00:01 | 
|  7 |        16 |    16 |      16 |       9 | 11111 | 2013-11-29 16:53:17 | 
+----+-----------+-------+---------+---------+-------+---------------------+
3 rows in set (0.00 sec)

結果は以下の通りでした!

  • 数値型の場合は 0 だけが偽であとは真
  • 文字列の場合は空文字列および '0' が偽、あとは真
  • タイムスタンプの場合は '0000-00-00 00:00:00' (つまり unix time でゼロ) だけが偽で、あとは真

なお手元でビルドした MySQL 5.0.95, 5.1.72, 5.5.34 については全て同じ結果となりました。MySQL 5.6.14 はデフォルトだと型に対して不正なデータ(桁数オーバーとか 1970-01-01 00:00:01 UTC 以前のタイムスタンプ値とか)はエラーになって受け入れてくれなかった*3ので、そこは以前のバージョンでfallbackする値に書き換えてやりましたが、結果は同じでした。

文字列がウッという感じですねー。でもやっぱりちょっとアレなので、0 か 1 だけを入れてフラグにするケースではちゃんと 1 と同値かどうかを比較するようにしたほうがいいと思います!

おまけ: MySQL 4.0編

mysql> CREATE TABLE testdata (
    -> `id` INT PRIMARY KEY NOT NULL auto_increment,
    -> `flag_bool` BOOL,
    -> `num_b` TINYINT(1),
    -> `num_int` INT,
    -> `num_dec` DECIMAL(1),
    -> `str` VARCHAR(16),
    -> `ts` TIMESTAMP
    -> ) Engine=InnoDB charset='utf8';
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''utf8'' at line 9

ウッ

mysql> INSERT INTO testdata (flag_bool,num_b,num_int,num_dec,str,ts) VALUES (FALSE,0,0,0,'','0000-00-00 00:00:00');
ERROR 1054: Unknown column 'FALSE' in 'field list'

アアアアアッ

おまけ2: 本日の怪談

なぜこんな話を書いたかというとですね。前に仕事してたら話題になったんですよ。
そのときにざっと調べたら、5.1では数値型でも 1 以外は偽になってた気がするんですよねー……。それでびびってこのネタはAdvent Calendarネタだ!!!!! と思ったんですけど。今日やってみたら全部真じゃないですか。どういうことだ。
sql_modeかとも思ったけどそういうのを制御するパラメータないっぽいし。。。。。。。

ひー。

かじゅある!

明日(12/2)は @myfinder さんが書きますよ!

*1:http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html

*2:また 1970-01-01 00:00:01 UTC より前の値は全部ゼロ扱いで 0000-00-00 00:00:00 に落とされてますね。入力値はローカルタイムゾーン、つまりJSTで解釈されていることに注意。

*3:しらなかった………!!!