【Ruby】MySQLで日付型のデータを扱う際の注意点（でも実際はSQLモードの問題・・・）

前置き

Ruby から mysql2 gem を使って、MySQL へアクセスをいろいろ試してみよう、、、と思ったのですが、Qiita の記事で、こんなのを見つけてしまったので、こちらについていろいろ調べてみました。

Ruby mysql2 で prepareする際の注意点 - Qiita

この記事で使用したアプリのバージョン情報はこちらです。

• ruby 2.6.6p146 (2020-03-31 revision 67876) [x64-mingw32]
  • mysql2 (0.5.3 x64-mingw32)
• mysql Ver 8.0.22 for Win64 on x86_64 (MySQL Community Server - GPL)

どういうことか？

リンク元の記事の内容をまとめると、下記の通りです。

• MySQL では、DATE型、DATETIME型のカラムには「Zero値」を入れられる。
• MySQL側のテーブル内のデータに、日付型のZero値が含まれている場合、そのデータを prepared statement で取得しようとすると、エラー「invalid date (ArgumentError)」となってしまう。
• prepared statement ではなく、query メソッドで取得する場合は、エラーにならず、nil として取得できる。

これって・・・けっこう重大な話なんじゃ・・・と思いつつ、詳しく調べてみました。

MySQLの日付型に入れられるデータ

MySQL では、設定がデフォルトのままの場合、通常では日付と判断されない日付を入れることができます。

• Zero値 : 0000-00-00 00:00:00
• 日のZero値 : 2020-12-00 00:00:00
• 月のZero値 : 2020-00-15 00:00:00

詳しくは、MySQL のリファレンスに書いてあります。バージョンが 5.6 のものですが、バージョン 8 も同様です。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3 日付と時間型

MySQL では、DATE または DATETIME カラムに、日がゼロ、または月および日がゼロである日付の格納を許可しています。これは、正確な日付がわかっていない可能性のある生年月日を格納する必要があるアプリケーションで役立ちます。この場合は、単に日付を '2009-00-00' または '2009-01-00' として格納します。このような日付を格納する場合は、DATE_SUB() や DATE_ADD() などの完全な日付を必要とする関数で正しい結果が返されることは期待しないでください。日付でゼロの月または日の部分を無効にするには、NO_ZERO_IN_DATE SQL モードを有効にします。

---

MySQL では、「ダミーの日付」として '0000-00-00' の「ゼロ」の値を格納できます。場合によっては、これは、NULL 値を使用するよりも便利であり、使用するデータおよびインデックススペースが少なくなります。'0000-00-00' を無効にするには、NO_ZERO_DATE SQL モードを有効にします。

---

・・・「ダミーの日付」として Zero値が入れられる、というのは、まぁそれも有りか、という気もしますが・・・月や日に Zero値が入れられるのって、はっきり言って「余計な仕様つけるんじゃねーよ！」とツッコみたくなる内容です。月や日が未定だったら、それに対応できるようにシステム・データベース設計するべきだし。日付型のところに、明らかに日付エラーとなる値がしれっと入る方が、よっぽど迷惑なんですけど・・・

MySQL側のデータ準備

というわけで、MySQL側でデータを準備します。今回は、テーブル名datetest に、数値型のid列、datetime型の‘date‘列を作り、確認しておきたいデータを格納します。

mysql> DESC datetest ;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| date  | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.06 sec)
mysql> SELECT * FROM datetest ORDER BY id;
+------+---------------------+
| id   | date                |
+------+---------------------+
|    1 | 2020-11-24 21:30:50 |        # 通常の日付
|    2 | 0000-00-00 00:00:00 |        # Zero日付
|    3 | NULL                |        # Null値
|    4 | 2020-11-00 00:00:00 |        # 日のみ Zero の日付
|    5 | 2020-00-01 00:00:00 |        # 月のみ Zero の日付
|    6 | 0000-01-01 00:00:00 |        # 年のみ Zero の日付（これは日付エラーにならないデータ）
+------+---------------------+
6 rows in set (0.00 sec)

このデータを、Ruby の mysql2 を使って取得してみます。

時刻データの取得

データの取得は、query メソッドを使用するか、prepare メソッドで SQL文を準備した後、execute メソッドで実行する（prepared statement) か、の２通りがあります。

# query メソッドを使う場合
results = client.query("SELECT id, date FROM datetest WHERE id = #{id}")

# prepared statement を使う場合
statement = client.prepare("SELECT id, date FROM datetest where id = ?;")
results = statement.execute(id)    

この２つの方法で、datetest テーブルの date 列の値を取得したときの結果が、下の表のようになりました。

日付	query	prepared
2020-11-24	〇	〇
0000-00-00	△*1	×*2
NULL	〇(nil)	〇(nil)
2020-11-00	× *3	× *4
2020-00-01	× *5	× *6
0000-01-01	〇	〇

結果まとめ

・・・まとめると・・・

• query メソッド、prepared statement のどちらも、「日付 Zero値」、「月 Zero値」、「日 Zero値」は正しく取得できない。
  • 「日付 Zero値」を query メソッドで取得した場合のみ、nil値に変換され、例外は発生しないが、他の Zero値は、すべて例外が発生する。
• query メソッド、prepared statement で、例外が発生するタイミングが異なる。また、例外の種類も違う。
  • query メソッドの例外 mysql2::Error は、結果に対して each メソッドを実行したときに発生する。
  • prepared statement の例外 ArgumentError は、execute メソッドを実行したときに発生する。

何が問題か？

問題は、大きく2点かな、と思っています。

1. MySQL側の日付型の列に、日付として正しくないデータが入ってしまうこと
   MySQL サービスをデフォルトの状態で運用すると、日付Zero値が入ることを許容した設定になってしまいます（version 8 でも同じ）。これ自体が、他のデータベースでは普通に考えられないような仕様です。

2. mysql2 モジュール側で、日付エラーとなるデータを正しく捌けないこと
   MySQL側で 日付Zero値が許容されていても、Ruby側のTime型では、そんな値は許容されていないので、そのまま値を取り込むこと自体、無理な訳です。日付エラーの場合は、無理やりString型で取り込む、なんてことをしたら、収拾つかなくなりそうですし。 それでも「日付型のチェックでエラーなら nil値」で取得できればよかったのですが、さすがに mysql2 モジュールではそこまで想定されてないようです。もしくは、そんな変な仕様許さない！かもしれないですが。

対策

MySQL の SQLモードで、日付Zero値を許容しない設定に変更する

Oracle や SQL Server など、他のデータベースでは、日付型の列に Zero値なんて入らないわけで。そのように MySQL でも動作するように、SQLモードを変更してしまいます。

というか、特別な理由がない限り、そうした方が絶対に良い、と私は思います。 変更方法については、次の記事で。

日付型の列に、すでに日付Zero値が入ってしまっていて、どうしても変えられない場合

この場合は、アプリケーション側で何とかするしかないわけで・・・

• SQL文で日付型の列を取得する際、文字列で取得するようにする。

SELECT DATE_FORMAT( date, '%Y%m%d') as date from datetest;

上記のように、MySQL側で文字列に変換したデータを取得するようにすれば、日付Zero値も文字列として取得することができるので、あとはアプリケーション側でどうとでも捌くことができるようになります。

でも、これはあくまでデータベース側の設定が変えられない事情がある場合であって、通常は MySQL のSQLモードを変更して、システム設計する方が良いはずです。

---

*1:nil値として取得

*2:executeメソッドを実行したときに例外：ArgumentError:invalid date

*3:queryの結果に対して eachメソッドを実行したときに例外：Mysql2::Error:invaild date in field 'date'

*4:executeメソッドを実行したときに例外：ArgumentError:mday out of range

*5:queryの結果に対して eachメソッドを実行したときに例外：Mysql2::Error:invaild date in field 'date'

*6:executeメソッドを実行したときに例外：ArgumentError:mon out of range