Example sharing of problems caused by MySQL 8.0 timestamp

This article brings you some related problems that may arise regarding the default value of fields in mysql. I hope it will be helpful to you.

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

Judging from the error message, it may be that the modify_time field does not have a default value set or the default value is set incorrectly.

Next, check the table structure:

CREATE TABLE `jj_xxxx` (
....
  `create_time` timestamp NOT NULL DEFAULT '1999-12-31 23:00:00' ,
  `update_user` int DEFAULT NULL,
  `modify_time` timestamp NOT NULL DEFAULT '1999-12-31 23:00:00',
 ....
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4893 DEFAULT CHARSET=utf8 COMMENT='xxxxx'

Judging from the table structure, there seems to be no problem with the default value set. Checking the setting of the sql_mode parameter, it seems that no problem has been found;

The same is true for the online table reported by business personnel Yes, but it is normal online, but now we need to migrate this business to other environments. From the business to the database is another set of environments;

Suddenly we took 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 value set by the parameter explicit_defaults_for_timestamp in the two versions is different;

Reason:

explicit_defaults_for_timestamp system variable determines the MySQL server's response to timestamp Different handling of default and NULL values ​​in columns.

This variable was introduced from MySQL version 5.6.6. It is divided into global level and session level. It can be dynamically updated. The default value is OFF.

In 8.0, the default value was changed to on

explicit_defaults_for_timestamp=OFF, which means using the default The default format of timestamp; what is the default format of timestamp type?

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

2. The timestamp field in the first column, if it is not forced to be specified If the default value or on update attribute is used, it will be set to DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP by default.

3. For the timestamp field in the non-first column, if the default value is not forced to be specified, DEFAULT '0000-00-00 00:00:00'

4. Insert null value into this column , will be automatically converted to the default value;

explicit_defaults_for_timestamp=ON, then turn off the timestamp default feature:

1. If not null is not specified explicitly, the default is null;

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

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

You need to carefully consider the following scenarios:

1. timestamp not null default CURRENT_TIMESTAMP ,What business impact will it bring when explicit_defaults_for_timestamp changes from 0 to 1?

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

2. Convert datetime default null to timestamp default CURRENT_TIMESTAMP, what business impact will it bring?

Doing such field conversion will convert all the original null values ​​​​of the field into CURRENT_TIMESTAMP. If there is a lot of historical data, such conversion is very resource-consuming. At the same time, it is also necessary to consider the impact of value changes on the business.

Recommended learning: mysql video tutorial

The above is the detailed content of Example sharing of problems caused by MySQL 8.0 timestamp. For more information, please follow other related articles on the PHP Chinese website!