Why MySQL 5.7 timestamp not null requires default value?

When I try to create a table with non null timestamp, it requires me to add a default timestamp to it or use the implicit default. But I am not sure why this is necessary.

Have a look at https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c615bce0cd1009b0c597593e73fdd794,

where the following SQL
create table t3 (
id int not null,
d1 timestamp not null,
num double not null)
engine=innodb;


will be transformed to
CREATE TABLE t3 (
id int(11) NOT NULL,
d1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
num double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1


When using such tools, we can clearly see that int and double does not auto generate a default value to it, but timestamp did. What is the reason behind why timestamp requires a default, while the other does not?

Personally, I expect there are no default, and when the end user insert a data into the row without d1 timestamp, then an exception will be thrown and such row will not be able to inject to the table. But clearly this does not seem to be the idea MySQL is taking.

It is specifically mentioned on the explicit_defaults_for_timestamp

If explicit_defaults_for_timestamp is disabled, the server enables the
nonstandard behaviors and handles TIMESTAMP columns as follows:

The first TIMESTAMP column in a table, if not explicitly declared with
the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is
automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON
UPDATE CURRENT_TIMESTAMP attributes.

This behavior only occur for the first TIMESTAMP column when explicit_defaults_for_timestamp is disabled.

mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.7.16-0ubuntu0.16.04.1-log |
+-----------------------------+

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
    
mysql> create table t3 (
    -> id int not null,
    -> d1 timestamp not null,
    -> d2 timestamp not null,
    -> num double not null)
    -> engine=innodb;
Query OK, 0 rows affected (0.31 sec)

mysql> show create table t3;

  CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `d1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `d2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `num` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As per the question

When using such tools, we can clearly see that int and double does not
auto generate a default value to it, but timestamp did. What is the
reason behind why timestamp requires a default, while the other does
not?

MySQL handles timestamp differently from the other datatypes.

Personally, I expect there are no default, and when the end user
insert a data into the row without d1 timestamp

This can be done by enabling explicit_defaults_for_timestamp

mysql> set session explicit_defaults_for_timestamp = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> drop table t3;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t3 (
    -> id int not null,
    -> d1 timestamp not null,
    -> num double not null)
    -> engine=innodb;
Query OK, 0 rows affected (0.18 sec)

mysql> show create table t3;

 CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `d1` timestamp NOT NULL,
  `num` double NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
1 row in set (0.00 sec)

StackExchange Database Administrators Q#314898, answer score: 5