MySQLのsql_modeのせいで'0000-00-00'登録時にエラーが出た昔話。 #MySQL

みんな知っているのだろうか、mysqlにはsql_modeという設定があることを。
かなりハマったのでメモ公開。

まず公式の文書はこちら。
https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html

この中で大事な設定として以下の３つがピックアップされています。

ANSI

このモードは、構文および動作が標準の SQL にさらに緊密に準拠するように変更します。ANSI モードは、外部参照 S(outer_ref) を持つ設定関数 S が、外部参照が解決される外部クエリー内で集約できない場合のクエリーに、サーバーがエラーを返します。このようなクエリーを次に示します。

STRICT_TRANS_TABLES、STRICT_ALL_TABLES

厳密モード、厳密SQLモードとも呼ばれる。
厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。

STRICT_ALL_TABLES では、MySQL はエラーを返し、残りの行を無視します。ただし、それより前の行が挿入または更新されているため、結果は部分更新となります。これを防ぐには、テーブルを変更することなく中止できる単一行ステートメントを使用します。
STRICT_TRANS_TABLES では、MySQL は無効な値をカラムについてのもっとも近い有効な値に変換し、調整された値を挿入します。値が欠落している場合、MySQL はカラムデータ型の暗黙のデフォルト値を挿入します。いずれの状況でも MySQL はエラーでなく警告を生成し、ステートメントの処理を続行します。暗黙的なデフォルトについては、セクション11.6「データ型デフォルト値」に記載されています。

トランザクション有無によっても挙動が変わります。

TRADITIONAL

MySQL を「従来型の」 SQL データベースシステムのように動作させます。このモードを簡単に説明すると、カラムに不正な値を挿入したときに「警告ではなくエラーを返し」ます。

確かにこれらは大事なのだが今回ハマったのはこれではない。

日付：0000-00-00 00:00:00 は登録可能か？

これが今回言いたかったことです。
これが登録可能かどうかはsql_modeの設定によって変わります。
（結論から先に言うと、普通にMySQLをインストールしてれば登録可能です。）

NO_ZERO_DATE、NO_ZERO_IN_DATE

NO_ZERO_DATE モードは、サーバーが '0000-00-00' を有効な日付として許可するかどうかに影響します。この影響は、厳密 SQL モードが有効かどうかにも依存します。

このモードが有効でない場合、'0000-00-00' は許可され、挿入によって警告が生成されません。
このモードが有効な場合、'0000-00-00' は許可され、挿入によって警告が生成されます。

では実験。

SQLモードの設定

SQLモードの設定はSETステートメントを使います。GlobalとSessionが設定可能です。

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

SQLモードの確認

SELECTで確認出来ます。
MySQL5.6.6以降はデフォルトで「NO_ENGINE_SUBSTITUTION」が設定されています。
それ以前は空白です。

以降はMySQL5.6.36 を前提とします。

mysql> SELECT @@GLOBAL.sql_mode;
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

実験開始

ではテーブルに試しに0000-00-00を入れてみましょう。

まずテーブル作ります。

mysql> create table hoge (test_datetime datetime, test_date date);
Query OK, 0 rows affected (0.13 sec)

mysql> desc hoge;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| test_datetime | datetime | YES  |     | NULL    |       |
| test_date     | date     | YES  |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

で、insert。

mysql> insert into hoge values ('0000-00-00 00:00:00', '0000-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from hoge;
+---------------------+------------+
| test_datetime       | test_date  |
+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 |
+---------------------+------------+
1 row in set (0.00 sec)

おー入った。

nullは？

mysql> insert into hoge values (null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from hoge;
+---------------------+------------+
| test_datetime       | test_date  |
+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
+---------------------+------------+
2 rows in set (0.00 sec)

おー入った。

では、sql_modeのNO_ZERO_DATEを有効にしてみましょう。

mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------+
| @@SESSION.sql_mode                  |
+-------------------------------------+
| NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION |
+-------------------------------------+
1 row in set (0.00 sec)

で、0000-00-00をinsert

mysql> insert into hoge values ('0000-00-00 00:00:00', '0000-00-00');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from hoge;
+---------------------+------------+
| test_datetime       | test_date  |
+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
| 0000-00-00 00:00:00 | 0000-00-00 |
+---------------------+------------+
3 rows in set (0.00 sec)

mysql> insert into hoge values (null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from hoge;
+---------------------+------------+
| test_datetime       | test_date  |
+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
+---------------------+------------+
4 rows in set (0.00 sec)

0000-00-00を登録するとwarningが出ました。

じゃあ厳密SQLモードも設定してみましょう。

mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,STRICT_TRANS_TABLES ';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+---------------------------------------------------------+
| @@SESSION.sql_mode                                      |
+---------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------+
1 row in set (0.00 sec)

で、insert。

mysql> insert into hoge values ('0000-00-00 00:00:00', '0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'test_datetime' at row 1

mysql> insert into hoge values (null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from hoge;
+---------------------+------------+
| test_datetime       | test_date  |
+---------------------+------------+
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
| 0000-00-00 00:00:00 | 0000-00-00 |
| NULL                | NULL       |
| NULL                | NULL       |
+---------------------+------------+
5 rows in set (0.00 sec)

エラーが出ましたね。
まあ普通は設定しないけど、0000-00-00でエラーが出る場合はこれを疑ってみましょう。