この記事はZOZO Advent Calendar 2023 #8 20日目の記事になります。

はじめに

MySQLでNOT NULLの日付型カラムを追加する際に追加カラムに指定される値が気になったのでDEFAULT指定有り、無しで挙動を確認してみました。

準備

初めに適当なテーブルを用意してデータをINSERTします

mysql> CREATE TABLE
    ->     test_shop (
    ->         id BIGINT UNSIGNED PRIMARY KEY NOT NULL COMMENT 'ID',
    ->         tag_name VARCHAR(60) COMMENT ''
    -> ) COMMENT = 'test_shop';
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_shop (id,tag_name) values (1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_shop (id,tag_name) values (2,'test2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_shop (id,tag_name) values (3,'test3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_shop;
+----+----------+
| id | tag_name |
+----+----------+
|  1 | test1    |
|  2 | test2    |
|  3 | test3    |
+----+----------+

DEFAULT指定有りのカラム追加

ではまずDEFAULT指定有りのカラム追加を実行してみます。

mysql> ALTER TABLE test_shop ADD tast_at1 DATETIME(6) NOT NULL DEFAULT '9999-12-31 00:00:00.000000' COMMENT 'test';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_shop;
+----+----------+----------------------------+
| id | tag_name | tast_at1                   |
+----+----------+----------------------------+
|  1 | test1    | 9999-12-31 00:00:00.000000 |
|  2 | test2    | 9999-12-31 00:00:00.000000 |
|  3 | test3    | 9999-12-31 00:00:00.000000 |
+----+----------+----------------------------+

自動で全てのレコードにDEFAULT指定した値が設定されました。

DEFAULT指定無しのカラム追加

次にDEFAULT指定無しのカラム追加をして行きます。

mysql> ALTER TABLE test_shop ADD tast_at2 DATETIME(6) NOT NULL COMMENT 'test2';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'tast_at2' at row 1

エラーになりました。
'0000-00-00 00:00:00'が正しくないと怒られてしまいました。

調べるてみるとDEFAULTを指定しないと暗黙的なデフォルトが指定され、日付型では '0000-00-00 00:00:00'が指定されるようです。
（詳細はリファレンスをご確認ください。11.2 日時データ型、11.6 データ型デフォルト値)

そして日付の'0000-00-00'はSQLモードによって有効な日付として許可されなくなるようで、そのためにエラーになっているようです。

現状のSQLモードを確認してみると。

mysql> SELECT @@SESSION.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

このようになっており、調べてみると以下が設定されていると'0000-00-00'が許可されなくなるようです。
　・STRICT_TRANS_TABLES
　・NO_ZERO_IN_DATE
　・NO_ZERO_DATE

なので上記のSQLモードを外します。

mysql> SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

外してから上で実行したカラムの追加を行うと、

mysql> ALTER TABLE test_shop ADD tast_at2 DATETIME(6) NOT NULL COMMENT 'test2';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_shop;
+----+----------+----------------------------+----------------------------+
| id | tag_name | tast_at1                   | tast_at2                   |
+----+----------+----------------------------+----------------------------+
|  1 | test1    | 9999-12-31 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
|  2 | test2    | 9999-12-31 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
|  3 | test3    | 9999-12-31 00:00:00.000000 | 0000-00-00 00:00:00.000000 |
+----+----------+----------------------------+----------------------------+

カラムを追加することができました。
値には暗黙的デフォルト値の'0000-00-00 00:00:00'が設定されています。

※厳密にはNO_ZERO_IN_DATE、NO_ZERO_DATEを設定していても、STRICT_TRANS_TABLESを外せば'0000-00-00'は警告が出るだけでデータを入れることはできるようです。
詳細はリファレンスをご確認ください。
5.1.11 サーバー SQL モード

最後に

'0000-00-00'を許可するためにSQLモードの変更など行いましたが、'0000-00-00'はあまり現実的な日付ではないので基本的にはDEFAULT指定をしてカラム追加するのが良いかなと思いました。