The timestamp of "MySQL" inquiry

Background

The Start_time,end_time time field in the previous Business feedback table was automatically updated over time, which is not the business will, and the serious point is that it is going to fail.

There are date,datetime,timestamp time types in MySQL

See what the official documents say.

The date type is used for values with a, a date part, and no time part. MySQL retrieves and displays DATE values in ' yyyy-mm-dd ' format. The supported range is ' 1000-01-01 ' to ' 9999-12-31 '. The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' yyyy-mm-dd HH:MM:SS ' format. The supported range is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-19 03:14:07 ' UTC.

Here we highlight the next datatime and Timestmap

Business feedback Start_time,end_time missing on the timestamp type, combined with the characteristics of timestamp, we analyze the reasons.

Automatic initialization and Updating for TIMESTAMP

What do you mean?

CREATE TABLE Gbtest_with_force_default_val (    ID bigint auto_increment,    t_null_1 timestamp,    t_null_2 Timestamp,    t_with_not_null timestamp not NULL,    t_with_not_null_default timestamp not null default ' 2016-12-21 ' ,    primary key (ID));mysql> show create TABLE gbtest_with_force_default_val; CREATE TABLE ' gbtest_with_force_default_val ' (  ' id ' bigint () not NULL auto_increment,  ' t_null_1 ' timestamp Not null default Current_timestamp in UPDATE current_timestamp,  ' t_null_2 ' TIMESTAMP not null default ' 0000-00-00 00:0 0:00 ',  ' t_with_not_null ' timestamp not null DEFAULT ' 0000-00-00 00:00:00 ',  ' T_with_not_null_default ' Timestamp not NULL DEFAULT ' 2016-12-21 00:00:00 ',  

In the MySQL5.5 version, the timestamp feature

1. TIMESTAMP Word defaults that is not NULL if you are defining "T_null_1 TIMESTAMP default NULL" will elevate "ERROR 1067 (42000): Invalid default value for ' T_null_1 '. can be specified as NULL null, "T_null_1 TIMESTAMP null", at which point the statement can be added to change the default value.
2. If no special instructions are made, the default Current_timestamp on UPDATE Current_timestamp property is set on the first TIMESTAMP field in the same table, and the second field sets the default ' 0000-00-00 00:00:00 ', if the specified "DEFAULT current_timestamp on UPDATE current_timestamp" is displayed on two fields, it will be told "ERROR 1293 (HY000): Incorrect table definition; There can is only one TIMESTAMP column with the Current_timestamp in DEFAULT or on UPDATE clause ", Test discovery does not have this limitation in the MySQL5.6 version.

Mysql> select version (); +------------------+| Version ()        |+------------------+| 5.6.16.7-rc0-log |+------------------+1 row in Set (0.00 sec) CREATE TABLE ' Gbtest_ With_force_default_val_null ' (  ' id ' bigint () not NULL auto_increment,  ' t_null_1 ' timestamp NOT NULL default Current_timestamp on update current_timestamp,  ' t_null_2 ' TIMESTAMP not null DEFAULT current_timestamp on update CURR Ent_timestamp,  ' t_with_not_null ' TIMESTAMP not null DEFAULT ' 0000-00-00 00:00:00 ',  ' T_with_not_null_default ' Timestamp not NULL default ' 2016-12-21 00:00:00 ',  PRIMARY KEY (' id ')) engine=innodb default Charset=utf8

How to solve the problem of business, in fact, is very simple, since "default Current_timestamp" There is a limit, then do not need to be good, with null DEFAULT current_timestamp remove the Automatic update properties, Or explicitly allow the two fields to be null.

CREATE TABLE Gbtest_with_force_default_val_null (     ID bigint auto_increment,     t_null_1 timestamp null Current_ TIMESTAMP,     t_null_2 TIMESTAMP null DEFAULT 0,     t_with_not_null TIMESTAMP not NULL,     T_with_not_null_default Timestamp NOT NULL default ' 2016-12-21 ', primary key (ID));mysql> Show CREATE TABLE Gbtest_with_force_default_val_null ; CREATE TABLE ' gbtest_with_force_default_val_null ' (  ' id ' bigint () not NULL auto_increment,  ' T_null_1 ' Timestamp null current_timestamp,  ' t_null_2 ' timestamp null DEFAULT ' 0000-00-00 00:00:00 ',  ' t_with_not_null ' Timestamp NOT NULL default ' 0000-00-00 00:00:00 ',  ' t_with_not_null_default ' timestamp not null default ' 2016-12-21 00 : 00:00 ',  PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

Timestamp features in MySQL5.6 version

After learning, found in the 5.6 version of a system variable called "Explicit_defaults_for_timestamp", but the default is off, but also can support a table in the simultaneous updating of multiple fields.

Mysql> Show variables like '%explicit_defaults_for_timestamp% '; +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| Explicit_defaults_for_timestamp | OFF   |+---------------------------------+-------+1 row in Set (0.00 sec)

So what's the difference between opening this parameter?

Mysql> SET @ @global. explicit_defaults_for_timestamp=on;   ERROR 1238 (HY000): Variable ' Explicit_defaults_for_timestamp ' is a read only Variable This parameter does not support dynamic modification!!! CREATE TABLE Gbtest_with_noforce (    ID bigint auto_increment,    t_null_1 timestamp,    t_null_2 timestamp,    T_with_not_null timestamp NOT NULL,    t_with_not_null_default timestamp not null default ' 2016-12-21 ',    Primary Key (ID)); CREATE TABLE ' Gbtest_with_noforce ' (    ' id ' BIGINT () not NULL auto_increment,    ' t_null_1 ' TIMESTAMP NULL DEFAULT N ULL,    ' t_null_2 ' TIMESTAMP null DEFAULT null,    ' t_with_not_null ' TIMESTAMP not NULL,    ' T_with_not_null_ Default ' TIMESTAMP not NULL default ' 2016-12-21 00:00:00 ',    PRIMARY KEY (' id ')) engine=innodb default Charset=utf8;

The following changes were found:

1. Default parameter is NULL
2. Does not automatically add properties such as Default Current_timestamp on UPDATE current_timestamp

Conclusion

That consideration of version compatibility, the test found that in the 5, 5 version of the table created by default properties, in the 5.6 version of the migration is not any problem, is fully compatible. This means that, with a later version of Mysql5.6, the Explicit_defaults_for_timestamp parameter should be set to true immediately, and the behavior of developing timestamp is promptly fed back.

The timestamp of "MySQL" inquiry