1

I've googled this for a bit and can't find anything on it.

Why does the MySQL column type "TIMESTAMP" require the "NULL" parameter to accept null values, when other column types dont?

Also, is this the only column type that requires the "NULL" parameter to accept null values?

Thank you.

Improve this question
14
  • I thought this SO post might help. It mentions that MySQL timestamps are actually UNIX timestamps internally. Commented Jan 29, 2016 at 1:27
  • 2
    @TimBiegeleisen What does that have to do with the default of the NULL option? That has nothing to do with the internal representation. Commented Jan 29, 2016 at 1:41
  • 1
    @TimBiegeleisen As far as I know, NULL is always implemented using an extra flag in the column. There's no null value of INT, for instance. Commented Jan 29, 2016 at 1:45
  • 1
    Looks like it's version-dependent. In 5.5 it defaults to NOT NULL, in 5.6 it's NULL. Commented Jan 29, 2016 at 1:48
  • 1
    @TimBiegeleisen All you did was provide an irrelevant link. Your train of thought only emerged later, after questioning. Commented Jan 29, 2016 at 2:47

1 Answer 1

Reset to default
4

This is related to a system variable that was added in 5.6.6 and later, explicit_defaults_for_timestamp. The default behavior is:

  • TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

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

  • TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned 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.

Setting this variable makes TIMESTAMP columns behave like other columns. The plan is that in some future release the non-standard behavior will be removed entirely, and the variable will then be deprecated.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That's confusing. in your sqlfiddle 5.6 default to NULL, in my 5.6 default to NOT NULL
explicit_defaults_for_timestamp = off in my 5.6
Thank you! Just what I was looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.