Timestamp in MySQL 5.6 have those changes _mysql

The default behavior of timestamp before MySQL 5.6.6:

Timestamp column if the null attribute is not explicitly declared, the default is not NULL. (Other data types, if not declared as NOT NULL, allow null values.) Sets the timestamp column value to null and is automatically stored as the current timestamp.

The first TIMESTAMP column in the table, if no null property, default, or on update is declared, the DEFAULT current_timestamp and on Update Current_timestamp properties are automatically assigned.

The second timestamp column in the table, which is automatically assigned ' 0000-00-00 00:00:00′ if it is not declared null or the default clause. Inserting a row does not indicate the value of the Change column, which is assigned the ' 0000-00-00 00:00:00′ by default and has no warning.

2 column timestamp default behavior that is not declared null

The method for starting this default setting from MySQL5.6.6 is discarded. The following warning appears when MySQL starts:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Use the--explicit_defaults_for_timestamp server Option
(seedocumentation for more details).

Turn off warnings and add in my.cnf

[Mysqld]
Explicit_defaults_for_timestamp=true

After restarting MySQL, the error disappears, then timestamp's behavior is as follows:

Timestamp if the declaration not NULL is not displayed and is a null value, you can set the column to NULL instead of the default fill behavior.

The default Current_timestamp and on UPDATE current_timestamp properties are not assigned by TIMESTAMP.

Mysql5.6-innodb-timestamptimestamp is not set to NULL

A timestamp column that is declared NOT NULL and has no default clause is not a default value. When you insert a column into a datasheet without assigning a value to the timestamp column, if you are in strict SQL mode, an error is thrown, and if the strict SQL mode is not enabled, the column is assigned a value of ' 0000-00-00 00:00:00′ and a warning appears. (This is the same as MySQL processing other time type data, such as DateTime)

TIMESTAMP default set to not NULL

Note:

The above content is independent of the storage engine selection.

MySQL upgrades from 5.5 to 5.6,timestamp changes

Objective

Some time ago, the system MySQL upgraded from 5.5 to 5.6, the system has a large number of anomalies. Most of the anomalies are caused by changes in timestamp behavior.

The behavior of timestamp in MySQL5.5

The first timestamp not NULL character De Yin default value that does not set a default value:

Current_timestamp on UPDATE current_timestamp the TIMESTAMP not null word De Yin default value that does not have a default value set:

An implicit default value is used when the 0000-00-00 00:00:00 TIMESTAMP null field is inserted as:

Current_timestamp does not support multiple current_timestamp default values

The behavior of timestamp in MySQL5.6

Supports multiple current_timestamp default values that can be compatible with 5.5 behavior and supports implicit defaults

Explicit_defaults_for_timestamp=0 I test the installation of the MySQL5.6 by default use this parameter, when starting, the server will give a warning.

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use–explicit_defaults_for_timestamp server option (the documentation for more details).

You can remove the implicit default value

Explicit_defaults_for_timestamp=1

Summarize

The behavior of timestamp in MySQL5.5 is strange, it can cause some hidden problems, such as the application of NULL value in the program MySQL5.6 can make timestamp behavior normal, but there are compatibility problems explicit_defaults_for_ Timestamp parameters will disappear in the future. We do not rely too heavily on the attributes of the database, which can cause constraints to the application