explicit_defaults_for_timstampのONとOFFでエラーになるケースならないケース

TL;DR

explicit_defaults_fot_timestamp	カラムのデフォルト値	INSERTでカラム未指定	INSERTでNULLを指定
ON	なし	エラー	エラー
ON	あり	OK	エラー
OFF	なし	エラー	OK
OFF	あり	OK	OK

---

explicit_defaults_for_timestamp というパラメーターがあって、これは「昔の古いTIMESTAMP型の挙動、SQL標準じゃないからちゃんとエラーにするようにする」というパラメーター。

これ自体は5.6で導入されていて、 “explicit_defaults_for_timestamp” でググるとワーニングの消し方(OFFだとエラーログにワーニングが出るので、ONにすれば消えるよ、というネタ)はいっぱい出てくるんだけど、結局どんなクエリーが影響を受けるのかイマイチまとまってるのを見つけられなかったのでメモ。

MySQL 8.0からはデフォルトでONになったことだし。

デフォルト値なしのカラム定義

mysql57 5> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

explicit_defaults_for_timestamp = OFF

mysql57 5> SELECT @@session.explicit_defaults_for_timestamp;
+-------------------------------------------+
| @@session.explicit_defaults_for_timestamp |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql57 5> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql57 5> INSERT INTO t1 (num) VALUES (2);
ERROR 1364 (HY000): Field 'ts' doesn't have a default value

mysql57 5> SELECT * FROM t1;
+-----+---------------------+
| num | ts                  |
+-----+---------------------+
|   1 | 2020-01-10 19:13:54 |
+-----+---------------------+
1 row in set (0.00 sec)

explicit_defaults_for_timestamp = ON

mysql57 5> SELECT @@session.explicit_defaults_for_timestamp;
+-------------------------------------------+
| @@session.explicit_defaults_for_timestamp |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql57 5> INSERT INTO t1 VALUES (1, NULL);
ERROR 1048 (23000): Column 'ts' cannot be null

mysql57 5> INSERT INTO t1 (num) VALUES (2);
ERROR 1364 (HY000): Field 'ts' doesn't have a default value

デフォルト値ありのカラム定義

mysql57 5> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

explicit_defaults_for_timestamp = OFF

mysql57 5> SELECT @@session.explicit_defaults_for_timestamp;
+-------------------------------------------+
| @@session.explicit_defaults_for_timestamp |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql57 5> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql57 5> INSERT INTO t1 (num) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql57 5> SELECT * FROM t1;
+-----+---------------------+
| num | ts                  |
+-----+---------------------+
|   1 | 2020-01-10 19:18:24 |
|   2 | 2020-01-10 19:18:27 |
+-----+---------------------+
2 rows in set (0.00 sec)

explicit_defaults_for_timestamp = ON

mysql57 5> SELECT @@session.explicit_defaults_for_timestamp;
+-------------------------------------------+
| @@session.explicit_defaults_for_timestamp |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql57 5> INSERT INTO t1 VALUES (1, NULL);
ERROR 1048 (23000): Column 'ts' cannot be null

mysql57 5> INSERT INTO t1 (num) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql57 5> SELECT * FROM t1;
+-----+---------------------+
| num | ts                  |
+-----+---------------------+
|   2 | 2020-01-10 19:18:08 |
+-----+---------------------+
1 row in set (0.00 sec)

というわけで最初のまとめどおり、

explicit_defaults_fot_timestamp	カラムのデフォルト値	INSERTでカラム未指定	INSERTでNULLを指定
ON	なし	エラー	エラー
ON	あり	OK	エラー
OFF	なし	エラー	OK
OFF	あり	OK	OK

​