default value for timestamp in mysql

Asked Nov 12 '20 at 08:05

Active Nov 12 '20 at 11:56

Viewed 38 times

mysql version is 5.7.20

create table test (
  start_day                     timestamp,
  create_time                   timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  update_time                   timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

this sql can execute well.

create table test (
  start_day                     timestamp,
  create_time                   timestamp,  
  update_time                   timestamp
);

Otherwise, this sql shows 1067 - Invalid default value for 'create_time'. In first sql, I found that the default value for start_day is CURRENT_TIMESTAMP, and it will update automatically. Can someone explain what is the default value in the second sql and what is the difference between the two sql.

edited Nov 12 '20 at 11:56

asked Nov 12 '20 at 08:05

John Smith

The code is correct itself. [fiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e348b91f27b291ea5d689360bb114a0f). Show `SELECT @@sql_mode` output. – Akina Nov 12 '20 at 08:14
Why would create_time have an UPDATE? – Strawberry Nov 12 '20 at 08:16
@Akina the first sql is correct, why the second is wrong – John Smith Nov 12 '20 at 11:58
@Strawberry just for test – John Smith Nov 12 '20 at 11:59
Investigate [explicit_defaults_for_timestamp system variable](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp) description. – Akina Nov 12 '20 at 12:05

default value for timestamp in mysql

0 Answers0