Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the problems caused by MySQL 8.0 timestamp". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Today, the business reported a problem. The modify_time field is not allowed to be null, while the business feedback field is set to a default value. The specific business error information is as follows:

From the error message, it may be that the default value of the modify_time field is not set or the default setting is incorrect.

Next, take a look at the table structure:

CREATE TABLE `jj_ xxxx` (.... `update_ time` timestamp NOT NULL DEFAULT '1999-12-31 23 update_ int DEFAULT NULL, `modify_ time` timestamp NOT NULL DEFAULT' 1999-12-31 2300 int DEFAULT NULL,.... PRIMARY KEY (`goods_ id`) ENGINE=InnoDB AUTO_INCREMENT=4893 DEFAULT CHARSET=utf8 COMMENT='xxxxx'

From the point of view of the table structure, there seems to be no problem with setting the default value. Check the setting of the sql_mode parameter, and it seems that no problem has been found.

The business staff feedback that the online table is the same, but it is normal online, and at present, to migrate the business to another environment, from the business to the database is another environment.

Suddenly take into account the differences in database versions; the new environment for migration is MySQL version 8.0, while the online environment is version 5.7.The default values of parameter explicit_defaults_for_timestamp settings in the two versions are not the same.

Reason:

The explicit_defaults_for_timestamp system variable determines how the MySQL server handles the 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.

The default value was changed to on in 8.0.

Explicit_defaults_for_timestamp=OFF, which means that the default timestamp default format is used; what is the default format of the timestamp type?

1. Unlike other field types, this field defaults to not null. And it is not allowed to set default null.

2. The first column timestamp field will be set to DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP by default if you do not force the default value or on update attribute to be specified.

3. If the timestamp field is not the first column, if the default value is not forced, DEFAULT '0000-00-0000: 00 00'

4. Insert a null value into the column and it will be automatically converted to the default value

Explicit_defaults_for_timestamp=ON, then turn off the features of timestamp default:

1. If the specified not null is not displayed, it defaults to null

2. The default value will also be null instead of CURRENT_TIMESTAMP

3. If the not null attribute is specified, the inset type does not specify the value of the field, and an error will be reported under strict sql_mode. Insert '0000-00-0000: 00' under non-strict sql_mode

The following scenarios need to be carefully considered:

1. Timestamp not null default CURRENT_TIMESTAMP, what is the business impact when explicit_defaults_for_timestamp is changed from 0 to 1?

Such a conversion, if the timestamp field has a default value, will cause the original statement of the insert timestamp field value to be null to fail, affecting the business.

2. What will be the business impact when datetime default null is converted to timestamp default CURRENT_TIMESTAMP?

Doing such a field conversion will convert the original value of the field as null into CURRENT_TIMESTAMP, which is very resource-consuming if there is a lot of historical data. At the same time, we also need to consider the impact of the change in value on the business.

This is the end of the content of "how to solve the problems caused by MySQL 8.0 timestamp". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.