How to solve the timestamp 2038 problem of Mysql

The timestamp refers to the total number of seconds from 00:00:00 on January 1, 1970 Greenwich Time (08:00:00 on January 1, 1970, Beijing time) to the present.
Various versions of MySQL are deployed in the production environment, including three major versions of MySQL 5.5/5.6/5.7 and N minor versions. Due to poor upward compatibility of MySQL, the same SQL behaves differently in different versions. , the timestamp data type will be introduced in detail from several aspects below.

Time stamp data access

In the above three major versions of MySQL, the value range of the default timestamp (Timestamp) type is ’1970-01-01 00:00:01&rsquo ; UTC to ’2038-01-19 03:14:07’ UTC, the data is accurate to the second level. This value range contains about 2.2 billion values, so the 4-byte INT type is used internally in MySQL to store the timestamp. Data:
1. When storing timestamp data, first convert the local time zone time to UTC time zone time, then convert the UTC time zone time to a millisecond value in INT format (using the UNIX_TIMESTAMP function), and then store it in the database.
2. When reading timestamp data, first convert the millisecond value in INT format to UTC time zone time (using FROM_UNIXTIME function), then convert it to local time zone time, and finally return it to the client.

In MySQL 5.6.4 and later versions, timestamp type data can be converted to the highest precision microsecond (one millionth of a second). The data type is defined as timestamp(N), and the value range of N is 0- 6. The default is 0. If you need to be accurate to milliseconds, set it to Timestamp(3). If you need to be accurate to microseconds, set it to timestamp(6). The cost of improving data accuracy is the increase in its internal storage space, but it is still not. Change the minimum and maximum value range of the timestamp type.

Time stamp field definition

The timestamp field definition mainly affects two types of operations:

• When inserting a record, the timestamp field contains DEFAULT CURRENT_TIMESTAMP, such as If the specific time data is not specified when inserting the record, the timestamp field value is set to the current time

• When the record is updated, the timestamp field contains ON UPDATE CURRENT_TIMESTAMP. If the specific time is not specified when the record is updated, For time data, set the timestamp field value to the current time

PS1: CURRENT_TIMESTAMP means using the CURRENT_TIMESTAMP() function to get the current time, similar to the NOW() function

According to the above two types of operations, the timestamp column can have four combination definitions, whose meanings are:

• When the field is defined as timestamp, it means that the field will not be inserted or updated when inserting or updating. It will be automatically set to the current time.

• When a field is defined as timestamp DEFAULT CURRENT_TIMESTAMP, it means that the field will only be assigned the current time when inserted and no value is specified, and will not be modified when updated and no value is specified.

• When the field is defined as timestamp ON UPDATE CURRENT_TIMESTAMP, it means that the field is assigned the value "0000-00-00 00:00:00" when it is inserted and no value is specified, and when it is updated and If no value is specified, it updates to the current time.

• When the field is defined as timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, it means that the field does not specify a value when inserting or updating, and it is assigned the current time.

PS1: There will be differences between the table creation statement executed in MySQL and the final table creation statement. It is recommended to use SHOW CREATE TABLE TB_XXX to obtain the table creation statement of the created table.

Differences in the use of timestamp fields in various MySQL versions

• In MySQL 5.5 and earlier versions, only one timestamp field can be defined as DEFUALT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP , but this restriction has been removed in MySQL 5.6 and MySQL 5.7 versions;

• In the MySQL 5.6 version, the default value of the parameter explicit_defaults_for_timestamp is 1, and in the MySQL 5.7 version, the default value of the parameter explicit_defaults_for_timestamp is 0 ;

• In MySQL 5.5 and MySQL 5.7 versions, the timestamp type defaults to NOT NULL, and in MySQL 5.6 version, the timestamp type defaults to NULL;

When the table creation statement is set to c1 timestamp,

• is equivalent to c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

• in MySQL 5.5

  Equivalent to c1 timestamp NULL DEFAULT NULL in MySQL 5.6;

• Equivalent to c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

# in MySQL 5.7

##When c1 timestamp default 0 is used in the table creation statement,

• is equivalent to c1 timestamp NOT NULL DEFAULT in MySQL 5.5 ‘0000-00-00 00:00: 00’;

• is equivalent in MySQL 5.6 to c1 timestamp NULL DEFAULT ‘0000-00-00 00:00:00’;

• Equivalent to c1 timestamp NOT NULL DEFAULT in MySQL 5.7 ‘0000-00-00 00:00:00’;

##PS1: The main differences between MySQL 5.6 and MySQL 5.7 Affected by the default value of parameter explicit_defaults_for_timestamp.

PS2: When the default value of the timestamp column is ’0000-00-00 00:00:00’, using the default value of "not within the timestamp value range" will not generate a warning. .

Exception caused by timestamp type

When the MySQL parameter time_zone=system, querying the timestamp field will call the system time zone for time zone conversion. However, due to the global lock problem in the system time zone, multiple concurrent large data accesses This will cause thread context to switch frequently, CPU usage to skyrocket, system response to slow down and suspended animation to occur.

Time stamp type and time type selection

In some "Database Guidance" documents, it is recommended to use the timestamp type instead of the datetime field. The reason is that the timestamp type uses 4 bytes, and the datetime field Use 8 bytes, but with the improvement of disk performance and the reduction of memory costs, in the actual production environment, using the timestamp type will not bring much performance improvement. Instead, it may be limited and affect the business due to the definition and value range of the timestamp type. use.

In MySQL 5.6.4 and later versions, the timestamp type (timestamp) data can be converted to the highest precision microseconds, and the time type (datetime) data can also be converted to the highest precision microseconds. The same is true for the time type (datetime). You can get the same effect as timestamp type, for example, define the field as dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); The access range of time type (datetime) is ’1000-01-01 00: 00:00.000000’ to ‘9999-12-31 23:59:59.999999’, which can better store the data of each time period.

Time stamp type usage suggestions

When you only care about the last update time of the data, it is recommended to define the timestamp column as TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

If you care about the creation time and update time, it is recommended to set the update time as the timestamp field, define the creation time as DAETIME or TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’, and display it when inserting the record. Formula to specify the creation time;

It is recommended to define only a single timestamp column in the table, and explicitly define the DEFAULT and ON UPDATE attributes;

Although the timestamp field can be assigned or updated in MySQL , but it is recommended to explicitly insert and update the timestamp column only when necessary;

It is recommended to set the time_zone parameter to a value outside the system, such as setting the Chinese region server to "8:00";

It is recommended to keep the MySQL offline test version and the online production version consistent.

Similarities and differences between Timestamp and datetime

Similar points:

• can be automatically updated and initialized, and the default display format is the same YYYY-MM-dd HH:mm :ss

##Difference: The time range of

• timestamp is: ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC, automatic time zone conversion, actual milliseconds stored, 4 bytes stored

• datetime time range: ‘1000-01 -01 00:00:00’ to ‘9999-12-31 23:59:59’, does not support time zone, 8-byte storage

Set automatic update of timestamp and date Time

When a piece of data is updated or a new piece of data is inserted without assigning values ​​to date and mydate, the two fields date and mydate will automatically default to the current time

CREATE TABLE `mytime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `mydate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

2038Question

When the time stored in timestamp is greater than '2038-01-19 03:14:07' UTC, mysql will report an error, because this is a problem of mysql itself, which means that timestamp has an upper limit. , if it exceeds, an error will naturally be reported. Some screenshots are as follows:

Solution

timestamp Although there is an upper limit, it saves the timestamp, so you don’t need to consider the time zone issue. If It is necessary to deal with the needs related to time zones. When solving the 2038 limitation, it is recommended to change the timestamp to an integer type to save the timestamp and then convert it in the program (this solution has not been implemented, it is just a suggestion, use with caution!! )

If you don’t need to consider the time zone issue, just replace timestamp with the datatime type, because the value range of datatime is much larger, you can see the picture above;

Replacement ideas:

1. Modify the name of the original field;

ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';

Create a new field of datatime type (create a new column to replace the original one);

ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';

Copy the data of the original field column to the new field column;

UPDATE `student` SET `entry_date` = `temp_entry_date`;

Delete the original column;

ALTER TABLE `student` DROP `temp_entry_date`;

The complete sql is as follows: (It should be noted that the default value of the original timestamp also needs to be added)

ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';
ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';
UPDATE `student` SET `entry_date` = `temp_entry_date`;
ALTER TABLE `student` DROP `temp_entry_date`;

The above is the detailed content of How to solve the timestamp 2038 problem of Mysql. For more information, please follow other related articles on the PHP Chinese website!