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.

C_Neth
  • 696
  • 2
  • 9
  • 23
  • I thought [this SO post](http://stackoverflow.com/questions/5169679/mysql-convert-timestamp-time) might help. It mentions that MySQL timestamps are actually UNIX timestamps internally. – Tim Biegeleisen Jan 29 '16 at 01:27
  • You appear to be mistaken. ['If neither NULL nor NOT NULL is specified, the column is treated as though NULL is specified'](https://dev.mysql.com/doc/refman/5.1/en/create-table.html). No exception for TIMESTAMP is mentioned. – user207421 Jan 29 '16 at 01:35
  • This is not true, a not-null column will not accept null value – SIDU Jan 29 '16 at 01:36
  • @SIDU Nobody has said anything about a not-null column. – user207421 Jan 29 '16 at 01:39
  • 2
    @TimBiegeleisen What does that have to do with the default of the `NULL` option? That has nothing to do with the internal representation. – Barmar Jan 29 '16 at 01:41
  • @Barmar If the internal representation of UNIX timestamp somehow precluded the use of NULL, then MySQL might have to do extra work to allow NULL. – Tim Biegeleisen Jan 29 '16 at 01:44
  • 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. – Barmar Jan 29 '16 at 01:45
  • @EJP When I run the query "ALTER TABLE player_info MODIFY COLUMN date_col TIMESTAMP" and then "SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = "player_info";" it tells me that the column can not accept null values, unless I put the "NULL" parameter in the initial query. This is only the case with the TIMESTAMP column from my experiences so far. – C_Neth Jan 29 '16 at 01:45
  • @TimBiegeleisen There is no evidence for that assertion in your link. – user207421 Jan 29 '16 at 01:46
  • 1
    Looks like it's version-dependent. In 5.5 it defaults to `NOT NULL`, in 5.6 it's `NULL`. – Barmar Jan 29 '16 at 01:48
  • @Barmar in my 5.6 it default to not null for timestamp – SIDU Jan 29 '16 at 01:53
  • @Barmar the OP presented to us, in an authoritative way, that `TIMESTAMP` somehow is being treated differently with regard to `NULL` values. It seemed logical to me that one possible explanation for this is that the internal representation is different, possibly due to its being modelled on UNIX timestamp. While I am wrong, the train of thought is reasonable. – Tim Biegeleisen Jan 29 '16 at 01:55
  • @SIDU sqlfiddle apparently has `explicit_defaults_for_timestamp = true` set, so it fooled me. – Barmar Jan 29 '16 at 02:13
  • 1
    @TimBiegeleisen All you did was provide an irrelevant link. Your train of thought only emerged later, after questioning. – user207421 Jan 29 '16 at 02:47

1 Answers1

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.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612