Understanding MySQL explicit_defaults_for_timestamp and Its Impact on TIMESTAMP Columns

After migrating a business system, developers encountered the error "Column 'create_time' cannot be null". The issue originates from the MySQL system variable explicit_defaults_for_timestamp , which determines how TIMESTAMP columns treat default values and NULL assignments.

MySQL provides two temporal data types: TIMESTAMP , ranging from 1970‑01‑01 00:00:01 UTC to 2038‑01‑19 03:14:07.999999 UTC, and DATETIME , ranging from 1000‑01‑01 00:00:00 to 9999‑12‑31 23:59:59 . Both can be auto‑initialized and updated to the current time.

The variable explicit_defaults_for_timestamp is OFF by default in MySQL 5.7 (enabling non‑standard behavior) and ON by default in MySQL 8.0. When OFF, TIMESTAMP columns without an explicit NULL declaration are treated as NOT NULL and automatically receive CURRENT_TIMESTAMP on insert or update. When ON, the server follows standard SQL rules: a TIMESTAMP column declared NULL can store NULL, and a NOT NULL column without a default will reject NULL inserts.

In the presented scenario, the table definition includes: `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间', When the variable was ON, inserting NULL into create_time caused the error, while the same operation succeeded when the variable was OFF because MySQL automatically replaced NULL with the current timestamp.

Testing steps:

1. explicit_defaults_for_timestamp = OFF

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+

mysql> create table time_off(id int, time timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table time_off;
+----------+---------------------------------------------------------------+
| Table    | Create Table                                                  |
+----------+---------------------------------------------------------------+
| time_off | CREATE TABLE `time_off` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+---------------------------------------------------------------+

mysql> insert into time_off values (1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from time_off;
+------+---------------------+
| id   | time                |
+------+---------------------+
| 1    | 2021-10-12 01:05:28 |
+------+---------------------+

Inserting NULL automatically stored the current timestamp, and subsequent updates also refreshed the column.

2. explicit_defaults_for_timestamp = ON

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+

mysql> create table time_on(id int, time timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table time_on;
+---------+---------------------------------------------------------------+
| Table   | Create Table                                                  |
+---------+---------------------------------------------------------------+
| time_on | CREATE TABLE `time_on` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+---------------------------------------------------------------+

mysql> insert into time_on values (1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from time_on;
+------+------+
| id   | time |
+------+------+
| 1    | NULL |
+------+------+

Here the NULL value was stored as NULL, and attempting to insert NULL into the previously created time_off table now fails with:

mysql> insert into time_off values (3,null);
ERROR 1048 (23000): Column 'time' cannot be null

The tests confirm that the error observed after migration was caused by the variable being set to ON, which enforces stricter handling of TIMESTAMP columns.

In conclusion, explicit_defaults_for_timestamp standardizes MySQL’s time‑related behavior, making it more strict and is slated for removal in future MySQL versions. Proper migration planning and thorough testing of such parameters are essential to avoid hidden pitfalls.