This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for
5.7 (and effectively disabled on
5.1) and it is enabled in
Quoting from above link:
(5.7) If explicit_defaults_for_timestamp is disabled, the server enables
the nonstandard behaviors and handles TIMESTAMP columns as follows:
- TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning
such a column a value of NULL is permitted and sets the column to
the current timestamp.
- 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.
- TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute,
are automatically declared as DEFAULT '0000-00-00 00:00:00' (the
“zero” timestamp). For inserted rows that specify no explicit value
for such a column, the column is assigned '0000-00-00 00:00:00' and
no warning occurs.
Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is
enabled, a default value of '0000-00-00 00:00:00' may be invalid.
Now the issue with
5.7 is that
NO_ZERO_DATE mode is enabled. This does not allow the default value of
0000-00-00 00:00:00 which as described in the documentation would be added as default value (if not explicitly declared) in timestamp columns following the first one.
8.0 still has the
NO_ZERO_DATE mode enabled, but the
explicit_defaults_for_timestamp envvar is by default enabled which according to the documentation it would add null as the default value see below (which will not cause any errors on table creation ):
(8.0) If explicit_defaults_for_timestamp is enabled, the server disables the
nonstandard behaviors and handles TIMESTAMP columns as follows:
- TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and
permit NULL values. Assigning such a column a value of NULL sets it to
NULL, not the current timestamp.
- The first TIMESTAMP column in a table is not handled differently from TIMESTAMP columns following the first one.
This behavior has also been discussed on MySQL's issue tracker but has marked as "Not a bug".
As @Akina mentioned in comments, do not rely on default values. Always write the full specification for the field.