MySQLのSQL_MODEと日付型への値の挿入 その1

「MySQLの日付型についてシリーズ」第２回は前回先送りした「SQLモード」の話。

SQLモードとはMySQLの動作を設定する機能で、
MySQL特有の挿入時の値変換をとめたり、「||」をパイプとして使えたり、
できて、他のRDBMSに動作を似せたり、MySQLの旧バージョンに動作を似せたりできる。
値変換の件は前回紹介したsakaik氏のブログに詳しいので詳しく書く必要はないと思う。

今回は日付型に関するSQLモードについて検証してみた結果を吐き出そうと思う。
「んなもんマニュアル読めばわかるっつーの」っていう方はどうぞお引取りください。
役立つ情報はほとんどないと言い切れます。

SQLモードの設定方法

１．
my.conf(my.ini)に以下のように記述した状態でMySQLを起動する。
sql-mode="設定したいSQLモードを「,」区切りで列挙"

２．
MySQL接続したときに、以下のSQLを先に実行する。
SET sql_mode = "設定したいSQLモードを「,」区切りで列挙";

ちなみにWindows msi版はデフォルトで「STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION」が設定されている。
インストールのときに設定するか聞かれるUIがあり、デフォルトではチェックが入っているが見逃しやすいかも。

日付に関係するSQLモード

「ALLOW_INVALID_DATES」
日付の完全チェックを行わずに、月は1から12まで、日は1から31までであることだけをチェックする。
つまり「2010-02-31」のような日付も許可する。

「NO_ZERO_DATE」
「0000-00-00」を許可しない。

「NO_ZERO_IN_DATE」
0月および0日を許可しない。

そして直接日付型とは関係ないが、重要なのが
「STRICT_TRANS_TABLES」および「STRICT_ALL_TABLES」。
指定された値をテーブルに挿入できないときにクエリの実行を中断する。
前者はトランザクションのストレージエンジンに対して、
後者はすべてのストレージエンジンに対して有効。
これらのいずれかが指定されている状態を「STRICTモード」と呼ぶ。

おさらいすると
通常、不正な日付を日付型に挿入しようとすると
MySQLが勝手に「0000-00-00」に変換して挿入してしまう。
実はここで警告を吐いているが、クエリ自体は成功しているので見逃しやすい。
これをSTRICTモードにすると、クエリの実行を中断してエラーを発生させる。
ということ。

ここからが本題

ではSTRICTモードと各日付型に関するSQLモードを組み合わせるとどうなるのか。
また、組み合わせないとどうなるのか。
いくつかのパターンで検証してみた。特に、実在しない日付周りを中心に。

今回の検証ではトランザクションを組む必要もないと思うので、
STRICTモードはすべて「STRICT_ALL_TABLES」で設定する。

テーブル定義

CREATE TABLE  `db1`.`tb1` (
`date` DATE NULL
) ENGINE = INNODB;

なお発行前に毎回テーブルを空にする。「DELETE FROM `db1`.`tb1`;」
また、ワーニングやエラーが発生した場合は「SHOW WARNINGS;」を発行する。

2010-02-31という実在しない日付の挿入を検証

SQLモードなし

mysql> SET sql_mode = "";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `db1`.`tb1` ( `date` ) VALUES ( "2010-02-31" );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'date' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
+------------+
| date       |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

警告あり、値は0000-00-00に変換

STRICTモードのみ

mysql> SET sql_mode = "STRICT_ALL_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `db1`.`tb1` ( `date` ) VALUES ( "2010-02-31" );
ERROR 1292 (22007): Incorrect date value: '2010-02-31' for column 'date' at row 1

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Error | 1292 | Incorrect date value: '2010-02-31' for column 'date' at row 1 |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
Empty set (0.00 sec)

エラーで挿入できず

ALLOW_INVALID_DATESのみ

mysql> SET sql_mode = "ALLOW_INVALID_DATES";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `db1`.`tb1` ( `date` ) VALUES ( "2010-02-31" );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
+------------+
| date       |
+------------+
| 2010-02-31 |
+------------+
1 row in set (0.00 sec)

値変換なし、警告なし、エラーなし

STRICTモード + ALLOW_INVALID_DATES

mysql> SET sql_mode = "STRICT_ALL_TABLES,ALLOW_INVALID_DATES";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `db1`.`tb1` ( `date` ) VALUES ( "2010-02-31" );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
+------------+
| date       |
+------------+
| 2010-02-31 |
+------------+
1 row in set (0.00 sec)

値変換なし、警告なし、エラーなし

NO_ZERO_DATEのみ

mysql> SET sql_mode = "NO_ZERO_DATE";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `db1`.`tb1` ( `date` ) VALUES ( "2010-02-31" );
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'date' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
+------------+
| date       |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

値変換あり、警告あり、エラーなし

これだけ見ると、「NO_ZERO_DATE」意味ないじゃんってことになりかねない。
なぜなら「0000-00-00」を許可してしまっているから。
が、「NO_ZERO_DATE」の本来の使い道はこうではない。

長くなってしまったので、次回に続けよう。

次回の宿題

・「NO_ZERO_DATE」の本来の使い道
・そもそも「0000-00-00」ってなんなんだぜ