Shulou(Shulou.com)06/01 Report--

Foreword:

The timestamp field is often used in MySQL. For example, when we need to record the time when a row of data is created or modified, we usually use the timestamp that is the timestamp field. This article mainly introduces the use of timestamp field and related parameters. I hope you can have a deeper understanding of timestamp after reading.

Introduction to 1.TIMESTAMP Field Typ

The timestamp field type can store time type data, and the time range that timestamp can store ranges from '1970-01-01 00-01.000000' to '2038-01-19 003-14 07.999999 the time stamp field occupies 4 bytes. In the MySQL5.7 version, timestamp can specify precision, that is, fsp in TIMESTAMP (fsp) can specify an optional value between 0 and 6 to represent fractional second precision. A value of 0 indicates that there is no decimal part, and if omitted, the default precision is 0.

Let's briefly test it:

# timestamp can specify precision mysql > CREATE TABLE `stu_ tb` (- > `id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key',-> `stu_ id` int (11) NOT NULL COMMENT 'student number',-> `stu_ name`varchar (20) DEFAULT NULL COMMENT 'student name',-> `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time' -> `update_ time` timestamp (2) NOT NULL DEFAULT CURRENT_TIMESTAMP (2) ON UPDATE CURRENT_TIMESTAMP (2) COMMENT 'modification time',-> PRIMARY KEY (`id`)->) ENGINE=InnoDB DEFAULT CHARSET=utf8 Query OK, 0 rows affected (0.04 sec) mysql > show create table stu_tb\ Graph * 1. Row * * Table: stu_tbCreate Table: CREATE TABLE `stu_ tb` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `stu_ id`int (11) NOT NULL COMMENT 'student ID' `update_ name` varchar (20) DEFAULT NULL COMMENT 'student name', `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time` timestamp (2) NOT NULL DEFAULT CURRENT_TIMESTAMP (2) ON UPDATE CURRENT_TIMESTAMP (2) COMMENT 'modification time', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > insert into stu_tb (stu_id,stu_name) values Query OK, 1 row affected (0.01sec) mysql > select * from stu_tb +-+ | id | stu_id | stu_name | create_time | update_time | +-+-- -+-+ | 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17 purge 33buret 51.63 | +-+- -+-+ 1 row in set (0.00 sec) 2. Parameters that affect the timestamp

There are two main parameters that affect the timestamp display, one is explicit_defaults_for_timestamp,time_zone. The following is a brief description of the impact of these two parameters on timestamps.

The explicit_defaults_for_timestamp parameter determines how the MySQL server handles default values and null values in the timestamp column differently. This variable, introduced from MySQL version 5.6.6, is divided into global level and session level, and can be updated dynamically. The default value is OFF.

When the explicit_defaults_for_timestamp parameter is set to OFF by default, the behavior is as follows:

By default, if the timestamp column does not explicitly specify the null attribute, the column is automatically appended with the not null attribute (while other types of columns are allowed to have null values if they are not explicitly specified not null). If a null value is inserted into this column, the column's value is automatically set to the current timestamp value. The first timestamp column in the table, if no null attribute or default value is specified, does not specify an ON UPDATE statement. Then the column is automatically added with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes. For other TIMESTAMP columns, if the specified NULL and DEFAULT properties are not displayed, it is automatically set to NOT NULL DEFAULT '0000-00-0000: 00 DEFAULT'. (of course, this is related to SQL_MODE, if the SQL_MODE contains' NO_ZERO_DATE', is not actually allowed to set its default value to '0000-00-0000: 00 NO_ZERO_DATE','.)

When the explicit_defaults_for_timestamp parameter is set to ON, the behavior is as follows:

If the timestamp column does not explicitly specify the not null attribute, the default column can be null, and when a null value is inserted into the column, the null is directly recorded instead of current timestamp. The DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes are not automatically added to the first timestamp column in the table. If the timestamp column is appended with the not null attribute and no default value is specified. At this point, if a record is inserted into the table, but no value is specified for the TIMESTAMP column, if strict sql_mode is specified, an error will be reported directly. If strict sql_mode is not specified, then '0000-00-0000: 00 strict sql_mode' is inserted into the column and a warning is generated.

The time_zone parameter specifies the database time zone. Time_zone is divided into global level and session level, and can be dynamically updated. The default is SYSTEM, which means that the database service time zone is the same as the system time zone. When MySQL stores timestamp timestamps, the time stored in the database is actually the time of UTC, and different times are displayed according to the specific time zone when the query is displayed. You can write the default_time_zone specified time zone in the configuration file, for example, the specified China time East eighth Zone can be written like this: default_time_zone='+8:00'. Here is a brief demonstration of the display of time in different time zones:

# initial default mysql > show variables like'% time_zone%' +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | CST | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec) mysql > select * from testdb.stu_tb +-+ | id | stu_id | stu_name | create_time | update_time | +-+-- -+-+ | 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17 purge 33buret 51.63 | +-+- -+-+ 1 row in set (0.00 sec) # changed to UTC time zone and reconnected found that the time of timestamp storage changed mysql > set global time_zone='+0:00' Mysql > show variables like'% time_zone%' +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | CST | | time_zone | + 00:00 | +-+ 2 rows in set (0.01sec) mysql > select * from testdb.stu_tb +-+ | id | stu_id | stu_name | create_time | update_time | +-+-- -+-+ | 1 | 1001 | dfdfa | 2020-01-07 09:33:51 | 2020-01-07 09JR 3315 51.63 | +-+- -+-+ 1 row in set (0.00 sec) 3.TIMESTAMP best practices

There are many variants of TIMESTAMP in use, some of which are commonly used as follows:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Refresh the TIMESTAMP DEFAULT CURRENT_TIMESTAMP to this data column when creating a new record and modifying an existing record

Set this field to the current time when you create a new record, but do not refresh it TIMESTAMP ON UPDATE CURRENT_TIMESTAMP when you modify it later

Set this field to 0 when you create a new record, and refresh it TIMESTAMP DEFAULT 'yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP when you modify it later

Set this field to the given value when you create a new record, and refresh it later when you modify it

In practice, it is recommended that you create a table like this:

CREATE TABLE `table_ name` (`increment_ id`primary key',... `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`increment_ id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

That is, each row of data contains creation time and modification time, the program writes data does not need to display the specified creation time and modification time, and is automatically filled by the default value.

With regard to timestamps, here are some tips on how to help you:

The data table has create_time and update_time timestamp fields, and the default values are set. For timestamp fields, it is recommended that you do not specify or insert the now () function when inserting data. The explicit_defaults_for_timestamp parameter is recommended to default to OFF. If China time is used, it is recommended that the time_zone be set to'+ 8 00'. Database time zones in different environments are recommended to be consistent, and do not change the database time zone at will. Data migration between different instances should pay attention to whether the time zone is the same. The program jdbc connection string recommends that you specify a time zone and be consistent with the database.

Summary:

This article introduces the usage of timestamp in detail, and we also briefly introduce several parameters that affect timestamps. I hope the practice and related suggestions on the use of timestamp timestamps will be helpful to you. In fact, these contents are recorded in the official documents, so you can read the official documents more often when you have nothing to do.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.