MySQLのオプションでexplicit_
先日、筆者が担当している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 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_
explicit_defaults_for_timestampの挙動確認
ではexplicit_
このオプションはバージョン5.
まずexplicit_
explicit_defaults_for_timestamp = ON の場合
explicit_の場合は意識することはあまりありません。テーブルを作成、変更時に明示的に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'で宣言し、データの挿入が実施されます。
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
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
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_の場合は非標準動作を有効にして、以下のような挙動となります。
まず、明示的にNULLABLEにしていないTIMESTAMP型の場合はNOT NULLなカラムになります。もしNOT NULLなカラムにNULLを挿入した場合は現在時刻に変換されて挿入されます。
最初のTIMESTAMP型のカラムがNULL属性、default値、ON UPDATE属性いずれも明示的でない場合、NOT NULL, DEFAULT CURRENT_
2つ目以降のTIMESTAMP型のカラムは、SQLモードがSTRICTモードでない場合にNULL属性、DEFAULT属性が明示的に宣言されていないときは、NOT NULLとDEFAULT '0000-00-00 00:00:00'が自動的に付与されます。
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_NO_しか設定されていませんでした。
ここで今回のようなALTER文を実行したところ、ALTER文の中身はコメントの追加のみであったため、DEFAULT値およびNULL属性が明示的に設定されておらず自動で設定されてしまい、NOT NULLやデフォルト値のついたテーブル定義の変更、および現在NULLのデータに現在時刻が挿入されてしまった、というわけでした。
まとめ
今回はexplicit_
単なるカラムコメント追加と思っていたものが思わぬ罠にはまってしまったので、みなさまも同様のことが起きないように注意深く実施してください。
より詳細については公式ドキュメントにも記載があるので、こちらもご覧ください。