MySQL Basics-Timestamp type

Timestamp data storage

1, timestamp value range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07  '  UTC;2, convert data to UTC time zone when storing timestamp data, then calculate the millisecond value, then store it in the table;3, The data is converted to the UTC time zone when the timestamp data is read, then converted to the local time zone and displayed to the user,4, the timestamp type uses 4 bytes to hold the data.

Timestamp field definition

1, the timestamp field contains the default Current_timestamp, which means that the current time is used to assign a value to the field when the record row is inserted, if no value is specified for the column. 2, the timestamp field contains on Update Current_timestamp, which indicates that the current time is used to update the field when the record is updated, if the event stamp column is updated. 3, when the field is defined as TIMESTAMP default Current_timestamp, indicates that the field is only given the current time when it is inserted and no value is specified, and is not modified when it is updated and no value is specified. 4, when the field is defined as TIMESTAMP on UPDATE Current_timestamp, indicating that the field is assigned a value when it is inserted and no value is specified"0000-00-00 00:00:00", updated to the current time when the value is updated and not specified. 5, when the field is defined as TIMESTAMP default current_timestamp on update Current_timestamp, indicating that the field does not specify a value when it is inserted or updated, the value is assigned to the current time. 6, the timestamp field is implicitly the default value of NOT NULL in MySQL 5.5 and MySQL 57, and the implicit default value in MySQL 5.6 is null. 7, in MySQL, the timestamp field can be explicitly inserted or updated. 8, you can define multiple timestamp columns in MySQL.

The effect of parameters on timestamps

when the parameter explicit_defaults_for_timestamp is set to 1 o'clock:1, timestamp column defaults to NULL if not explicitly specified as not NLL2, the first TIMESTAMP column in the table does not automatically assign the DEFAULT current_timestamp and on UPDATE current_timestamp properties, which need to be explicitly declared. 3, the default worth parameter Explicit_defaults_for_timestamp set to 0 o'clock is not automatically created when the timestamp column in the table is declared as NOT null:1, timestamp column if not explicitly specified as NLL, the default is not NULL2the default value of NULL is incremented if the timestamp column is explicitly specified as null3, the first TIMESTAMP column in the table, and the default Current_timestamp and on Update Current_timestamp properties are automatically assigned if you do not declare a null property, default, or on update. 4, the second timestamp column in the table, if it is not declared null or the default clause, is defined as default '0000-xx-xx xx:xx:xx′。 When inserting a row does not indicate the value of the column being changed, the Lieme thinks '0000-xx-xx xx:xx:xxno warning is generated. The default property of the timestamp column is heavily dependent on the parameter Explicit_defaults_for_timestamp setting, so it is recommended to display the properties of the declared timestamp column at creation time to avoid logic problems due to different parameter settings! 

Timestamp field differences in MySQL versions

Timestamp field differences in MySQL versions1, in MySQL5.5 and earlier, only one timestamp field can be defined for Defualt current_timestamp or on UPDATE current_timestamp, but in MySQL5.6 and MySQL5the limit was canceled in the. 7 release. 2, in MySQL5the default value for parameter Explicit_defaults_for_timestamp in version 6 is 1;3, in MySQL5the default value for parameter Explicit_defaults_for_timestamp in version 7 is 0;4, when set at C1 timestamp, in MySQL5. 5 is equivalent to ' C1 ' timestamp not NULL for DEFAULT current_timestamp on UPDATE current_timestamp; In MySQL5. 6 is equivalent to ' C1 ' timestamp null DEFAULT null; In MySQL5. 7 is equivalent to ' C1 ' timestamp not NULL for DEFAULT current_timestamp on UPDATE current_timestamp;5, when set at C1 timestampdefault0 o'clock, in MySQL5.5 is equivalent to ' C1 ' Timestamp not NULL DEFAULT'0000-00-00 00:00:00'; In MySQL5.6 is equivalent to ' C1 ' timestamp NULL DEFAULT'0000-00-00 00:00:00'; In MySQL5.7 is equivalent to ' C1 ' Timestamp not NULL DEFAULT'0000-00-00 00:00:00';

Time Stamp recommendations

1 , it is recommended to define the timestamp column as TIMESTAMP default current_timestamp on update current_timestamp in cases where only the last update time of the data is concerned; 2 ' 0000-00-00 00:00:00 ' , and specify the creation time explicitly when inserting records; 3 , it is recommended that you define only a single timestamp column in the table, explicitly defining the default and on update properties; 4 , it is recommended that you explicitly insert and update timestamp columns only if necessary 5, when Time_zone=system, query the timestamp field, will call the system time zone value to do time zone conversion, in high concurrency or large data volume, may trigger the CPU abnormal explosion.

MySQL Basics-Timestamp type