MySQLの0000-00-00 00:00:00は使ってはならない - そーだいなるらくがき帳
このエントリで、MySQLのゼロが含まれる日付け、いわゆるZero Dateについての問題点が色々挙げられているのを見かけたので、手短に対処法を述べておきたい。
Zero Dateが存在する理由
なぜそんな厄介なデータが存在するのかというのは、開発の経緯や互換性といった深淵な理由からなので気にしないで欲しい。まあ、人間は完璧ではないので、人間が作るプログラムも完璧ではないということだ。当然ながらSQL標準から外れているものは、例外的な使い方をしたい場合を除き、使うべきではない。アンチパターンも使い方次第という話もあるが、例外的な使い方は基本的に苦労が増えるので使うべきではない。
SQLモード
実は、Zero DateはSQLモードで禁止できる。SQLモードというのは、MySQLが過去のバージョンとの互換性や、あるいは他のデータベース製品との互換性を高めるために、SQLの振る舞いを変えるために使用するオプションである。詳しくはマニュアルを参照して欲しい。5.1.10 Server SQL Modes
NO_ZERO_DATEおよびNO_ZERO_IN_DATEを設定しておくこと、さらにSTRICTモードを使用することで、このZero Dateはテーブルへ格納することができなくなる。Zero Dateが問題だと思う人は、SQLモードを適切に設定しておいて欲しい。前者はすべてがゼロの日付けを禁止するもの、後者はゼロが含まれる日付け（例えば日の部分がゼロなど）を禁止するSQLモードである。いずれのパターンもゼロを含む日付けは世の中に存在しないので、セットで禁止しておくと良いだろう。
MySQL 5.7以降のバージョンでは、Zero Dateを許容しない設定がデフォルトとなっており、SQLモードを変更していない場合は対処は必要ない。（STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION）なので、今までよく分からず使っていたという人は、そのまま設定を触らないで欲しい。SQLモードはSQLの振る舞いを変えるものなので、よく分からないまま触るのは危険である。興味のある人はまずマニュアルを熟読し、理解した上で使用して欲しいと思う。SQLモードは他にもたくさん存在する。SQLモードの中には、集約されたものがあり、ひとつ指定することで他の複数のSQLモードを組み合わせたのと同じ効果になるものがあるのだが、TRADITIONALを指定している場合にはSTRICTモードかつZero Dateが弾かれる設定になっている。
MySQL 5.6以前のバージョンを使っているユーザーは明示的にSQLモードを設定することをおすすめしたいところだが、SQLモードを変更してアプリケーションのテストをやり直すぐらいなら、MySQLのアップグレードをした方が良いという気もしなくもない。SQLモードを変更する影響は甚大なので、手間を考えるとアップグレードしてしまったほうが良いからだ。ただ、Zero Dateが問題になるかどうかはアプリ次第なので、これまで問題なく動いているシステムなら、特に触る必要はないかも知れない。しかし、もし使っているバージョンがMySQL 5.5より古いものであれば、それは既に開発から非常に長い年月が経っており、既にバグ修正等は非常に乏しいフェーズに入っているので、5.5以前ならそういった意味ではやはりアップグレードをして欲しいところではある。
なお、NO_ZERO_DATEおよびNO_ZERO_IN_DATEは将来のバージョンでは廃止される予定である。いずれにせよSTRICTモードとセットでないと効果がないので、STRICTモードの一部になることが予定されているからである。
難敵IGNOREキーワード
NO_ZERO_DATEおよびNO_ZERO_IN_DATEとSTRICTモードを設定しても、完全にZero Dateを排除することはできない。警告やエラーを無視して強制的にSQLの実行を継続する、IGNOREというキーワードが存在するからだ。IGNOREを指定すると、例えば一意性制約等にひっかかってもそのSQLはすぐにエラーにはならず、実行が継続される。INSERTと組み合わせると、エラーが生じた場合はいくつかの行が挿入されないが、部分的に挿入されるというような結果になる。当然、OLTPのような処理では使うべきではないが、レポーティングなどで大まかな結果を知りたい場合に、計算の元になるテーブルを作るというような用途なら、エラーをスキップしたほうが便利だろう。IGNOREの使用は禁止できないので、アプリケーションの開発側で意図的に「使わないようにする」必要がある。IGNOREの効果については、古いバージョンではソースコード上の随所に実装が散らばってあり、統一性の無い部分があった。この点については、MySQL 5.7でリファクタリングが行われ、振る舞いが整理されたという経緯がある。詳しいことについては、拙著「詳解MySQL 5.7」に書いてあるので、持っている人は新機能167の項目を参照して欲しい。
IGNOREを使った場合でもZero Dateの利用を禁止したいという場合には、トリガーで防ぐことが可能である。どうしても何としてもZero Dateの侵入を防ぎたいという人はトリガーを作成して欲しいと思う。以下はトリガーの例である。
mysql> delimiter //
mysql> CREATE TRIGGER bi_tbl BEFORE INSERT ON tbl FOR EACH ROW BEGIN
IF NEW.dt_col = '0000-00-00 00:00:00' THEN
SIGNAL SQLSTATE '22007' SET MESSAGE_TEXT = 'Zero date is not allowed.', MYSQL_ERRNO = 1292;
END IF;
END;
mysql> delimiter ;
0 件のコメント:
コメントを投稿