Why MySQL 5.7 timestamp not null requires default value?

Question

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.

• mikkel: Maybe what you're looking to use is a datetime data type? I usually consider a timestamp an internal thing used for tracking creation/modifcation of rows (eg. for audits). For application data I would use a datetime - which also has a larger range.
• CHANist: Thanks, just find out that datetime data type is what I expected. See dbfiddle.uk/….
• Rohit Gupta: To me a timestamp field implies that it is the server timestamp, so the behaviour is what it should be.

Answer 1

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)

• CHANist: Thanks for the answer. But maybe you miss my point. I am not sure why MySQL needs to have a default timestamp value, whether it implicitly creates one, or require developer to explicitly specify one, is not my own concern. What my concern is, why it needs a default timestamp. Why MySQL just cannot allow my setting, and throw exception when data inserted have no timestamp column?

Answer 2

(Ergest has a good discussion of how things are. I'll address the why.)

Why? It is an old "feature". Abruptly removing the feature would cause migration problems.

In the old days, more than 2 decades ago, DEFAULT values were mostly provided by MySQL, and not explicitly provided. In particular TIMESTAMP was, by definition given DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP without specifying it.

Note: That was the only case of an implicit DEFAULT that was not a constant. (OK, AUTO_INCREMENT is another.)

Later, when DEFAULT was allowed to take more complex syntax, the designers needed to decide what to do with databases being upgraded from older versions. This led to clumsy things like explicit_defaults_for_timestamp as a compromise.