MySQL5.7からMySQL8.0へ移行するとデフォルトで日時のゼロ埋め値（0000-00-00 00:00:00）が利用できなくなると< a href=https://dev.mysql.com/doc/mysql-shell/8.0/ja/mysql-shell-utilities-upgrade.html>MySQLアップグレードチェッカユーティリティから傾向が出たので動作検証を行いました。
ゼロ埋め登録できてしまう
移行前のMySQL5.7(AWS RDS)でスナップショットを取得し、スナップショットの復元時にMySQL8.0へアップグレードしたところsql_modeは次のようになっていた。
mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.01 sec)
mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.01 sec)
この状態で日時のゼロ埋め値をINSERTしてみると登録できてしまう
mysql> INSERT INTO test(id, start_date ,end_date, text) VALUES (1, "0000-00-00 00:00:00","0000-00-00 00:00:00","テスト");
Query OK, 1 row affected (0.01 sec)
次にsql_modeに'NO_ZERO_IN_DATE,NO_ZERO_DATE'を設定してINSERTしてみる
mysql> SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT @@SESSION.sql_mode;
+------------------------------+
| @@SESSION.sql_mode |
+------------------------------+
| NO_ZERO_IN_DATE,NO_ZERO_DATE |
+------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test(id, start_date ,end_date, text) VALUES (1, "0000-00-00 00:00:00","0000-00-00 00:00:00","テスト");
Query OK, 1 row affected (0.01 sec)
これも登録できてしまう。
では今度はNO_ENGINE_SUBSTITUTIONも追加してみる。
こうすると意図通りにエラーとなる。
SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,STRICT_TRANS_TABLES';
mysql> SELECT @@SESSION.sql_mode;
+---------------------------------------------------------+
| @@SESSION.sql_mode |
+---------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test(id, start_date ,end_date, text) VALUES (1, "0000-00-00 00:00:00","0000-00-00 00:00:00","テスト");
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'start_date' at row 1
NO_ENGINE_SUBSTITUTIONは有効にしないと、エラーでレコードの作成を弾いてくれない模様。
下記はマニュアル「NO_ENGINE_SUBSTITUTION」の説明引用です。
NO_ENGINE_SUBSTITUTIONを無効にすると、CREATE TABLE については、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。ALTER TABLE では、警告が発生してテーブルは変更されません。
NO_ENGINE_SUBSTITUTIONを有効にすると、目的のエンジンが利用できない場合にエラーが発生し、テーブルは作成または変更されません。
マニュアル：https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html
