MySQL道普請便り

第196回MySQLのexplicit_defaults_for_timestampオプションによって意図せずデータとテーブル定義変更をしてしまう現象について

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文を実行すると、本来カラムのコメントだけが書き換わる想定だったのですが、実際は以下のようにテーブル定義とデータの書き換えが発生してしまいました。

想定していた変更後のテーブル定義
mysql> SHOW CREATE TABLE ts_t1\G
*************************** 1. row ***************************
       Table: ts_t1
Create Table: CREATE TABLE `ts_t1` (
  `id` int DEFAULT NULL COMMENT 'aaa',
  `ts1` timestamp NULL COMMENT 'bbb',
  `ts2` timestamp NULL COMMENT 'ccc'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='aaa'
実際の変更後のテーブル定義
mysql> SHOW CREATE TABLE ts_t1\G
*************************** 1. row ***************************
       Table: ts_t1
Create Table: CREATE TABLE `ts_t1` (
  `id` int DEFAULT NULL COMMENT 'aaa',
  `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'bbb', <- コメント以外にNOT NULLとDEFAULT値が追加
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'ccc' <- コメント以外にNOT NULLとDEFAULT値が追加
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='aaa'
1 row in set (0.01 sec)
データの中身
mysql> SELECT * FROM ts_t1;
+------+---------------------+---------------------+
| id   | ts1                 | ts2                 |
+------+---------------------+---------------------+
|    1 | 2023-05-01 22:00:00 | 2023-05-01 23:00:00 |
|    2 | 2023-05-01 23:18:47 | 2023-05-01 23:18:47 | <- NULLだったデータが現在時刻に置き換わっている
+------+---------------------+---------------------+
2 rows in set (0.00 sec)

上記の通り、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'で宣言し、データの挿入が実施されます。

STRICTモードでない場合にts1, ts2がNOT NULLのとき
mysql> show create table ts_t1\G
*************************** 1. row ***************************
       Table: ts_t1
Create Table: CREATE TABLE `ts_t1` (
  `id` int DEFAULT NULL,
  `ts1` timestamp NOT NULL,
  `ts2` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='aaa'
1 row in set (0.01 sec)

mysql> INSERT INTO ts_t1 VALUES (1, NULL, NULL);
ERROR 1048 (23000): Column 'ts1' cannot be null
まとめて挿入する場合はNULLがあってもエラーにならず'0000-00-00 00:00:00'が入る
mysql> INSERT INTO ts_t1 VALUES (1, '2023-05-01 14:00:00', '2023-05-01 15:00:00'),(2, NULL, NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2
TIMESTAMP型のカラムを指定しない場合はデフォルト値'0000-00-00 00:00:00'として挿入される
mysql> INSERT INTO ts_t1(id) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_t1;
+------+---------------------+---------------------+
| id   | ts1                 | ts2                 |
+------+---------------------+---------------------+
|    1 | 2023-05-01 14:00:00 | 2023-05-01 15:00:00 |
|    2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|    3 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
3 rows in set (0.00 sec)

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でSTRICTモードでない場合にtimestampを持つテーブルを作成
mysql> CREATE table ts_t1(id int, ts1 TIMESTAMP, ts2 TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table ts_t1\G
*************************** 1. row ***************************
       Table: ts_t1
Create Table: CREATE TABLE `ts_t1` (
  `id` int DEFAULT NULL,
  `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin'
1 row in set (0.00 sec)

mysql> INSERT INTO ts_t1 VALUES (1, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_t1;
+------+---------------------+---------------------+
| id   | ts1                 | ts2                 |
+------+---------------------+---------------------+
|    1 | 2023-05-01 19:57:05 | 2023-05-01 19:57:05 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

あらためて、なにが起こったのかを確認する

以上のことを踏まえて、改めて最初の事象を見てみます。

まず、このデータベースはexplicit_defaults_for_timestampがOFFで設定されており、SQLモードもNO_ENGINE_SUBSTITUTIONしか設定されていませんでした。

ここで今回のようなALTER文を実行したところ、ALTER文の中身はコメントの追加のみであったため、DEFAULT値およびNULL属性が明示的に設定されておらず自動で設定されてしまい、NOT NULLやデフォルト値のついたテーブル定義の変更、および現在NULLのデータに現在時刻が挿入されてしまった、というわけでした。

まとめ

今回はexplicit_defaults_for_timestampについて、筆者の経験をもとに説明しました。

単なるカラムコメント追加と思っていたものが思わぬ罠にはまってしまったので、みなさまも同様のことが起きないように注意深く実施してください。

より詳細については公式ドキュメントにも記載があるので、こちらもご覧ください。

おすすめ記事

記事・ニュース一覧