TIMESTAMP changes in MySQL 5.6

before MySQL 5.6.6, the default behavior of timestamp is:

• The timestamp column defaults to not nullif the NULL property is not explicitly declared. (While other data types, null values are allowed if no display is declared as NOT null.) Sets the timestamp column value to null, which is automatically stored as the current timestamp.
• The first timestamp column in the table, if no null property, default, or on updateis declared, automatically assigns default Current_timestamp and on update Current_ The TIMESTAMP property.
• The second timestamp column in the table, if not declared null or the default clause, is automatically assigned the ' 0000-00-00 00:00:00′. Inserting a row does not indicate a value for the column, which is assigned ' 0000-00-00 00:00:00′ by default and has no warning .

because it is empty by default, when inserting a value, there are two cases: the first column and the second column are Explicit_defaults_for_timestamp =false   [Default value]

Mysql> Create Table timestamp(IDint  not NULLAuto_increment, time1 timestamp,time2 timestamp,Primary Key(ID)) Engine=InnoDB; Query OK,0Rows Affected (0.27sec) MySQL> Insert  into timestamp(ID)Values(1); Query OK,1Row affected (0.21sec) MySQL> Select *  from timestamp;+----+---------------------+---------------------+|Id|Time1|Time2|+----+---------------------+---------------------+|  1 |  .- -- Geneva  -: A: - | 0000-xx-xx xx:xx:xx |+----+---------------------+---------------------+1Rowinch Set(0.19sec) MySQL>MySQL>ShowCreate Table timestamp;
CREATE TABLE`timestamp' (' ID 'int( One) not NULLauto_increment, ' time1 ' timestamp NOT NULL default Current_timestamp in UPDATE current_timestamp, ' time2 ' timestamp not null default ' 0000-0 0-00 00:00:00 ',PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=Utf8

2 column timestamp default behavior that is not declared null

UPDATE Current_timestamp Two species, the use of the following: 1 Current_timestamp  current_timestamp2onUPDATEcurrent_timestamp  UPDATE current_timestamp property. The value of the field is updated to the time of the current update operation, regardless of whether the value has changed.

The method of setting this default from MySQL5.6.6 is deprecated. The following warning appears when MySQL starts:

123	[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.Please use --explicit_defaults_for_timestamp server option(seedocumentation formoredetails).

Turn off warning, add in my.cnf

12	[mysqld]explicit_defaults_for_timestamp=true

The error disappears after restarting MySQL, when timestamp behaves as follows:

• Timestamp if you do not see the declaration not NULL, the null value is allowed, and you can directly set the column to NULL instead of the default padding behavior.
• The default Current_timestamp and on UPDATE current_timestamp properties are not assigned by TIMESTAMP.

Mysql>  Create TableTIMESTAMP1 (IDint  not NULLAuto_increment,time1timestamp, time2timestamp,Primary Key(ID)) Engine=InnoDB; Query OK,0Rows Affected (0.11sec) MySQL> Insert  intoTIMESTAMP1 (ID)Values(1); Query OK,1Row affected (0.18sec) MySQL>ShowCreate TableTimestamp1;CREATE TABLE' TIMESTAMP1 ' (' ID ')int( One) not NULLauto_increment, ' time1 'timestamp NULL DEFAULT NULL, ' time2 'timestamp NULL DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=Utf8MySQL> Select *  fromTimestamp1;+----+-------+-------+|Id|Time1|Time2|+----+-------+-------+|  1 | NULL  | NULL  |+----+-------+-------+1Rowinch Set(0.18Sec

TIMESTAMP is not set to NULL

• Timestamp columns that are declared as NOT null and do not have a default clause do not have a default value. Inserting a column into a datasheet without assigning a value to the timestamp column would throw an error if strict SQL mode is not enabled, and the column will be assigned a value of ' 0000-00-00 00:00:00′, along with a warning. (This is the same as when MySQL handles other time-type data, such as DateTime)

TIMESTAMP default setting is not NULL

Note:

The above content is not related to the storage engine selection.

TIMESTAMP changes in MySQL 5.6