MySQLのオプションでexplicit_defaults_for_timestampというオプションをご存知でしょうか? これはTIMESTAMP型の特定の非標準動作を有効にするかどうか、およびTIMESTAMP型のカラムでNULL値の処理を有効にするかどうかを決定するオプションになります。
先日、筆者が担当しているMySQLの運用において、このオプションによってデータおよびテーブル定義が意図しない形で書き換わってしまったことがありました。今回は同じような人が現れないように、このオプションについて解説します。
なにが起こったのか
mysql> SHOW CREATE TABLE ts_t1\G
*************************** 1. row ***************************
Table: ts_t1
Create Table: CREATE TABLE `ts_t1` (
`id` int DEFAULT NULL,
`ts1` timestamp NULL DEFAULT NULL,
`ts2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='aaa'
1 row in set (0.00 sec)
mysql> SELECT * FROM ts_t1;
+------+---------------------+---------------------+
| id | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2023-05-01 22:00:00 | 2023-05-01 23:00:00 |
| 2 | NULL | NULL |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
「このテーブルのカラムにコメントを追加したい」という要望があり、上のようなテーブルに対して以下のALTER文を実行してほしいという依頼を受けました。
ALTER TABLE ts_t1 MODIFY `id` int(11) COMMENT 'aaa'
, MODIFY `ts1` TIMESTAMP COMMENT 'bbb'
, MODIFY `ts2` TIMESTAMP COMMENT 'ccc';
このALTER文を実行すると、本来カラムのコメントだけが書き換わる想定だったのですが、実際は以下のようにテーブル定義とデータの書き換えが発生してしまいました。
上記の通り、TIMESTAMP型にNOT NULLとdefault値が追加されており、もともとNULLだったデータも現在時刻が入ってしまいました。
結論からいうと、これはexplicit_defaults_for_timestampがOFFであったため、明示的にNULLABLEを指定しなかったことでテーブル定義が変更されてしまったのでした。
explicit_defaults_for_timestampの挙動確認
ではexplicit_defaults_for_timestampについて、挙動を確認していきたいと思います。
このオプションはバージョン5.6から追加されており、5.7ではデフォルトOFF、バージョン8.0ではデフォルトONとなっています。また、バージョン8.0.18以降ではSYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN, SUPERのいずれかの権限をもっているユーザーのみが変更できるようになっています。
まずexplicit_defaults_for_timestampがONの場合の挙動を確認します。その後にexplicit_defaults_for_timestampがOFFの挙動も確認しましょう。
explicit_defaults_for_timestamp = ON
の場合
explicit_defaults_for_timestamp = ON
の場合は意識することはあまりありません。テーブルを作成、変更時に明示的にTIMESTAMP型にNOT NULLを指定なければNULLABLEなカラムになり、NULLを挿入することが可能になります。
また、default値を指定しなかった場合はdefault値を持たないカラムとして扱います。NOT NULLの場合にデータを挿入するときはSQLモードによって挙動がかわります。SQLモードがSTRICTモードで運用しているのであれば、INSERTはエラーとなります。
STRICTモードでない場合は単一のINSERTはエラーになりますが、NOT NULLなTIMESTAMP型のカラムを指定しなかったり、複数行を挿入するときにNULLがある場合は、暗黙的なデフォルトとして'0000-00-00 00:00:00'で宣言し、データの挿入が実施されます。
explicit_defaults_for_timestamp = OFF
の場合
explicit_defaults_for_timestamp = OFF
の場合は非標準動作を有効にして、以下のような挙動となります。
まず、明示的にNULLABLEにしていないTIMESTAMP型の場合はNOT NULLなカラムになります。もしNOT NULLなカラムにNULLを挿入した場合は現在時刻に変換されて挿入されます。
最初のTIMESTAMP型のカラムがNULL属性、default値、ON UPDATE属性いずれも明示的でない場合、NOT NULL, DEFAULT CURRENT_TIMESTAMP ,ON UPDATE CURRENT_TIMESTAMPが自動的に付与されます。
2つ目以降のTIMESTAMP型のカラムは、SQLモードがSTRICTモードでない場合にNULL属性、DEFAULT属性が明示的に宣言されていないときは、NOT NULLとDEFAULT '0000-00-00 00:00:00'が自動的に付与されます。
あらためて、なにが起こったのかを確認する
以上のことを踏まえて、改めて最初の事象を見てみます。
まず、このデータベースはexplicit_defaults_for_timestampがOFFで設定されており、SQLモードもNO_ENGINE_SUBSTITUTION
しか設定されていませんでした。
ここで今回のようなALTER文を実行したところ、ALTER文の中身はコメントの追加のみであったため、DEFAULT値およびNULL属性が明示的に設定されておらず自動で設定されてしまい、NOT NULLやデフォルト値のついたテーブル定義の変更、および現在NULLのデータに現在時刻が挿入されてしまった、というわけでした。
まとめ
今回はexplicit_defaults_for_timestampについて、筆者の経験をもとに説明しました。
単なるカラムコメント追加と思っていたものが思わぬ罠にはまってしまったので、みなさまも同様のことが起きないように注意深く実施してください。
より詳細については公式ドキュメントにも記載があるので、こちらもご覧ください。