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

前回は「2010/02/31」という実在しない日付を日付型カラムに挿入しようとしたときに、SQL_MODEによってどう変わるかを検証した。
今回は「0000/00/00」という実在しない日付を中心に取り扱ってみようと思う。

当然SQL_MODEの「NO_ZERO_DATE」が大いに関係してくる。

前回の続きということで、細かいことは省略していきなり検証結果を並べたい。

SQL_MODEなし

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-00-00" );
Query OK, 1 row affected (0.00 sec)

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

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

前回試した「2010/02/31」という実在しない日付はそのままでは警告が出てしまうのに対して、
「0000/00/00」という実在しない日付はそのままでも警告はでない。

NO_ZERO_DATEのみ

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-00-00" );
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として挿入されている。
警告上は値変換したことになっているが、実際には変わっていない。
この列はNULLを許可しているが、勝手にNULLになったりはしない。

STRICTモード + NO_ZERO_DATE

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-00-00" );
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date' at row
1
mysql> SELECT * FROM `db1`.`tb1`;
Empty set (0.00 sec)

エラーで挿入できず

前回「NO_ZERO_DATEの本来の使い道」としたのはこれだ。
つまり、STRICTモードと組み合わせないと本当の力は発揮してくれない。



まとめに入る前に回り道。

せっかくなので「NO_ZERO_IN_DATE」の動作も検証しておきたい。
取り扱うのは「2010-00-01」「0000-01-01」「0000-00-00」の3パターン

SQL_MODEなし

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "2010-00-01" );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-01-01" );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-00-00" );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `db1`.`tb1`;
+------------+
| date       |
+------------+
| 2010-00-01 |
| 0000-01-01 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

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

NO_ZERO_IN_DATEのみ

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "2010-00-00" );
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)

値変換あり、警告あり、エラーなし
これは予期しているとおり。

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-01-01" );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "0000-00-00" );
Query OK, 1 row affected (0.00 sec)

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

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

つまり「0000-01-01」のように年だけ0のものと、「0000-00-00」は無関係とわかる。

STRICTモード + NO_ZERO_IN_DATE

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

mysql> INSERT INTO `tb1` ( `date` ) VALUES ( "2010-00-00" );
ERROR 1292 (22007): Incorrect date value: '2010-00-00' for column 'date' at row
1
mysql> SELECT * FROM `db1`.`tb1`;
Empty set (0.00 sec)

当然エラーで挿入できず。


ここまでくると「SQL_MODEなしで警告が出ないパターンでは、STRICTモードでも変化はない。」というのが推測できているので、残りの部分はちょっと端折って回り道から戻っておこう。

「実在しない日付」とMySQLの蜜月についての私見

数字の桁だけがあっている「2010-13-34」のような文字列を日付型に格納できないのは、日付型のカラムはその文字列を格納するのではなく「日付」を格納しているはずだから当たり前といえば当たり前だ。
（INT型のカラムに文字列ではなく数値を格納するように）
ところが実際には「2010-02-31」のような実在しない日付を格納できてしまうし、「2010-00-00」とか「0000-00-00」という文字列すら警告も出さずに格納できてしまう。

これはINT型のカラムに「0」を入れるのとはわけが違う。
現代数学では0は実在してるし、逆にないと困る数字だ。
文字列型の列に""（空文字）を入れるのとも意味がやはり違う。（議論されやすいネタではあるが。）

まず一般的に西暦0年という年はない。西暦1年の前の年は紀元前1年になる。
（天文学的に0年を利用するケースもあるらしいが、グレゴリオ暦・ユリウス暦にはない）
0月とか0日にいたっては誰も疑いようもなく実在しない。

私はRDBMSに詳しいわけではないが、ちょっとだけ調べた限りではこれはMySQLの特色のひとつだと思う。
おそらく多くの場合「NULL」の代用として用いられている気がする。
そしてこれは「NULLを使いたくなければどうぞ」というNULL否定派へのMySQLなりの愛情なのかもしれない。

ここでNULLの是非を論じようとするほど私はRDBに造詣がないのでプロの方には見逃してほしいのだが、個人的には日付については「未定義」とか「UNKNOWN」をNULLにするのは間違っていないと思う。
（最初は自分も「0000-00-00が便利だ」と思っていた口ではあるが。）




余談まで挟んでまた無駄に長くなってしまったので、まとめの部分を次回に。
数年後に自分でこの記事を見て赤面していないことを祈る。