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

文 / @WordPress主题

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为好友
扫码添加UTHEME微信为好友
· 分享WordPress相关技术文章,主题上新与优惠动态早知道。
· 微信端最大WordPress社群,限时免费入群。