MySQLの暗黙の型変換の話

この記事はMySQL Casual Advent Calendar 2015の18日目です。
昨日はwinebarrelさんの「binlog_cache_sizeにメモリを食われた話」でした。
こういった実践の話は非常に貴重なので勉強になりました。

さて「PostgreSQLと比べてMySQLのいいところ書く！」を書こうと思ってたのですが昨日こんなツイートを見かけました。

"NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます" https://t.co/qLBYqy7FZm いや、できなくていいんですけど…。

— とみたまさひろ (@tmtms) 2015, 12月 17

@sakaik IS NULL と IS NOT NULL の両方に含まれます！

— とみたまさひろ (@tmtms) 2015, 12月 17

公式ドキュメントを見てみると…

NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます。

SELECT * FROM tbl_name WHERE date_column IS NULL

ODBC では '0000-00-00' 日付値がサポートされていないため、一部の ODBC アプリケーションを取得する際に、これが必要になります。

なるほど。
一部のODBCに対する優しさなんですね！！

そして僕もつい先日、MySQLの暗黙の型変換の罠優しさを味わったのでご紹介します。

-- テーブルの構造 `hoge`

CREATE TABLE IF NOT EXISTS `hoge` (
  `id` int(11) NOT NULL,
  `val` varchar(255) NOT NULL
);

mysql> SELECT * FROM hoge;
+----+--------+
| id | val    |
+----+--------+
|  1 | ONE    |
|  2 | 2      |
|  3 | Three3 |
|  4 | 4Four  |
+----+--------+
4 rows in set (0,00 sec)

こんなtableがあったとします。
そこで次のように検索してみましょう。

mysql> SELECT * FROM hoge WHERE val = "0";
Empty set (0,00 sec)

mysql> SELECT * FROM hoge WHERE val = 2;
+----+-----+
| id | val |
+----+-----+
|  2 | 2   |
+----+-----+
1 row in set, 3 warnings (0,00 sec)

想定した感じですね。

mysql> SELECT * FROM hoge WHERE val = 4;
+----+-------+
| id | val   |
+----+-------+
|  4 | 4Four |
+----+-------+
1 row in set, 3 warnings (0,00 sec)

あれ？文字列がマッチしましたね…
あっ！これPHPの文字列からintにキャストした時と一緒か！？

mysql> SELECT * FROM hoge WHERE val = 3;
Empty set, 3 warnings (0,00 sec)

あれ？Three3が出ない…

SELECT * FROM hoge WHERE val = 0;
+----+--------+
| id | val    |
+----+--------+
|  1 | ONE    |
|  3 | Three3 |
+----+--------+
2 rows in set, 3 warnings (0,00 sec)

Why,MySQL People！！

そしたら我らが@yoku0825さんが次のように教えてくれました。

val = 0の場合、0を文字列にキャストするのではなく、valをDOUBLEにキャストして0と比較しています。
英字で始まるSTRINGはDOUBLEにキャストできずにキャスト後の値が0になりますが、123abcのような文字列は「できるところまでキャストする」ので、キャスト後の値は123になります。
つまり123 <> 0です
ってことで、「数字で始まらないval」のものだけが検索に引っかかるわけです。
そして逆に数字から始まらない文字列はWHERE val=0に該当するわけです。

勉強になりますね！！
ちなみに僕が暗黙の型変換僕がハマったのはPHPのSESSIONをDBに保存している場合に検索しようとすると引っかかりました。
DBにSESSIONを保存することは多々あって例えばEC-CUBEやMagic3などのCMSも保存してるのでみなさんも検索の時はご注意ください。

ということで僕のMySQLエキスパートへの道は遠いようですｗ
それでは最後はとみたさんのお言葉で締めあせていただきます。

何年使ってても新鮮な驚きを与えてくれる飽きのこない RDBMS です。

— とみたまさひろ (@tmtms) 2015, 12月 17

みなさんも良いMySQLライフを！！