实例分享之MySQL 8.0 timestamp引发的问题

MySQL8.0引发的问题：timestamp默认值设置不正确

今天，我们收到了一个业务反馈，他们在MySQL8.0版本中遇到了一个问题，即modify_time字段不允许为null，而业务反馈这个字段是设置了默认值的。具体的业务报错信息如下所示：

ERROR 1048 (23000): Column 'modify_time' cannot be null

从报错信息看，可能是modify_time字段没有设置默认值或默认值设置的不正确。接下来，我们查看了一下表结构：

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'

从表结构看，设置的默认值好像没有什么问题。我们检查了一下sql_mode参数的设置，也没有发现什么问题。业务人员告诉我们，线上的表也是这样的，但是线上是正常的，而目前要把这个业务迁移到其他的环境，从业务到数据库是另外一套环境。我们忽然想到了数据库版本的差异。

迁移的新环境是MySQL8.0版本，而线上环境是5.7版本。两个版本中参数explicit_defaults_for_timestamp设置的默认值是不一样的。原因是explicit_defaults_for_timestamp系统变量决定MySQL服务端对timestamp列中的默认值和NULL值的不同处理方法。此变量自MySQL5.6.6版本引入，分为全局级别和会话级别，可动态更新，默认值为OFF。

在MySQL5.7版本中，参数explicit_defaults_for_timestamp默认是关闭的，而在MySQL8.0版本中，参数explicit_defaults_for_timestamp默认是开启的。因此，我们需要在MySQL8.0版本中显式地设置explicit_defaults_for_timestamp参数的值，以和线上环境保持一致。

解决方案如下：

1. 进入MySQL客户端，查看参数explicit_defaults_for_timestamp的值：

mysql> SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| explicit_defaults_for_timestamp | ON |
+--------------------------------+-------+

2. 如果参数explicit_defaults_for_timestamp的值为ON，说明MySQL8.0版本中timestamp字段的默认值设置受到影响。我们需要使用下面的命令关闭该参数：

mysql> SET @@global.explicit_defaults_for_timestamp = 0;

3. 进行完操作后，我们再次查看explicit_defaults_for_timestamp参数的值：

mysql> SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+--------------------------------+-------+

4. 最后，我们重新创建表，再进行一次测试，结果表明问题已经得到解决。

本文介绍了在MySQL8.0版本中，timestamp字段的默认值设置可能会受到系统变量explicit_defaults_for_timestamp的影响，导致无法正确设置默认值的问题，提供了解决方案，希望对大家有所帮助。

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。