MySQLが勝手にパースして日付や時間の型にしてくれる話 #MySQL

以下 MySQL5.6.41 でのお話。あと今日（これ書いてる日付）は2019年1月18日である。

突然だがこんな事ができる。

mysql> SELECT 20190118=CURRENT_DATE();
+-------------------------+
| 20190118=CURRENT_DATE() |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.01 sec)

mysql> SELECT '20190118'=CURRENT_DATE();
+---------------------------+
| '20190118'=CURRENT_DATE() |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

ちなみに1ってのはtrueである。もうちょっと詳細を出すとこんな感じである。

mysql> SELECT 20190118, CURRENT_DATE(), 20190118=CURRENT_DATE();
+----------+----------------+-------------------------+
| 20190118 | CURRENT_DATE() | 20190118=CURRENT_DATE() |
+----------+----------------+-------------------------+
| 20190118 | 2019-01-18     |                       1 |
+----------+----------------+-------------------------+
1 row in set (0.00 sec)

人によっては気持ち悪いと思うかもしれないが、日付をINT型にしているテーブルでもこういった比較が使えるのである。ちょっと便利。クライアント側とサーバ側でタイムゾーンが不一致のケースとか考えなくていいわけだ。

でも欠点もある。例えば「20190118」といった日付がDATE型以外で設定されたテーブルでパーティションを切ってたときは無効になったりする。

そもそもこの比較はどういった型にされているかというところであるが、MySQLのドキュメントでは比較時の型変換は以下のように書いてある

参考：https://dev.mysql.com/doc/refman/5.6/ja/type-conversion.html

演算子が別の型のオペランドとともに使用されると、オペランドの互換性を保つために型変換が発生します。一部の変換は暗黙的に発生します。たとえば、MySQL では必要に応じて数字が文字列 (またはその逆) に自動的に変換されます。

とのことだが

引数の一方が TIMESTAMP または DATETIME カラムで他方が定数の場合は、比較が実行される前に定数がタイムスタンプに変換されます。これは、ODBC により適合させるために実行されます。これは、IN() への引数には実行されません。念のため、比較を行う際は、常に完全な日付時間、日付、または時間文字列を使用してください。たとえば、日付または時間の値とともに BETWEEN を使用したときの結果を最適にするには、CAST() を使用して、明示的に値を目的のデータ型に変換します。

テーブル (複数可) からの単一行のサブクエリーは、定数とみなされません。たとえば、サブクエリーで DATETIME 値と比較される整数が返される場合は、比較が 2 つの整数として実行されます。整数は時間値には変換されません。オペランドを DATETIME 値として比較するには、CAST() を使用して、明示的にサブクエリーの値を DATETIME に変換します。

日付だけの場合（つまるところ CURRENT_DATE() の結果）はDATE型なのだが、DATETIME型がDATE型とTIME型の組み合わせのようなのでおそらくDATETIMEのルールが使用されているのだと思われる（参考:日付と時間型の概要）。

つまり、変換可能だと判断されると数値や文字列から勝手に変換されるわけである。ちなみに変換不可能ってどういうケースかというと場合によるらしい。例えば以下のケースは通る。

mysql> SELECT '2019:01:00'<CURRENT_DATE();
+-----------------------------+
| '2019:01:00'<CURRENT_DATE() |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

コロンを区切りとして取っているほか、「0日」という表現も許容しているわけである。

ただし「場合による」というのは設定によっても変わる。サーバのSQLモードによって厳密に取ったり取らなかったりする（参考:サーバSQLモード）。

サーバによって変わるので、これを使うべきかどうかはそのときに議論しておいたほうがいいだろう。DBを移行させると設定漏れが起きて動かなくなる可能性があるし、しかしDBを移行させること自体が発生するかどうかという話もある。また先ほど行ったとおり日付にパースされるので、インデックスが無効化されることもある。そうなるとネックになるので、必ずコード内に書く前に類似のSQLで一旦試してみることをおすすめする。