首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响

小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响

作者头像
bisal
发布2021-11-10 11:08:17
4.4K0
发布2021-11-10 11:08:17
举报

前几天读了一篇文章《故障分析 | MySQL 迁移后 timestamp 列 cannot be null》,没想到这两天就碰到了很相近的问题。

原文链接:https://mp.weixin.qq.com/s/nFO6xOuzfh8kOYDj99xE0g

同事说他通过某款商业数据同步软件将一个MySQL 5.7.28的库同步到MySQL 5.7.20的库时,如果表中含有TIMESTAMP数据类型、缺省值为current_timestamp的字段,这些表的同步任务就都失败了,而另外的一些包含了DATETIME数据类型的表就同步成功了,不知道这是不是MySQL版本差异导致的?

不通过软件,直接手工创建,不会报错,模拟的SQL,如下所示,一个主键id,外加两个timestamp类型的字段,都设置了默认值,

create table test(
  id int not null auto_increment, 
  createtime timestamp null default current_timestamp, 
  updatetime timestamp null default current_timestamp on update current_timestamp
);

同步软件报错的日志如下,提示为字段updatetime设置了无效的默认值,

ERROR_GENERAL "Handling new table 'test'.'flow' failed
execute create table statement failed, statement CREATE TABLE `test`.`test` ( `id` INT NOT NULL, `createtime` TIMESTAMP, `updatetime` TIMESTAMP ) COLLATE utf8_general_ci
[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.20-log]Invalid default value for 'updatetime'

细致的同学,可能会注意到,日志中记录的SQL语句显示createtime和updatetime都只声明了TIMESTAMP类型,缺少了原始建表语句中的NULL和DEFAULT属性,这会有什么影响?

《故障分析 | MySQL 迁移后 timestamp 列 cannot be null》这篇文章其实介绍过,MySQL中有个explicit_defaults_for_timestamp变量,他决定了MySQL是否为TIMESTAMP列的默认值和NULL值的处理启用某些非标准的行为,

230f8d00361ce37668bd94963dad5e0d.png
230f8d00361ce37668bd94963dad5e0d.png

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

如文档所说,如果explicit_defaults_for_timestamp=OFF ,服务器会启用非标准行为,并按以下规则处理TIMESTAMP列:

(1) 没有明确使用NULL属性声明的TIMESTAMP列会自动使用NOT NULL属性声明。给这样的列分配一个NULL的值是允许的,并将该列设置为current timestamp。

(2) 表中的第一个TIMESTAMP列,如果没有明确地用NULL属性,DEFAULT属性或ON UPDATE属性声明,将自动用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性声明。

(3) 在第一个列之后的TIMESTAMP列,如果没有明确地用NULL属性或明确的DEFAULT属性来声明,就会自动声明为DEFAULT '0000-00-00 00:00:00'。对于插入的行,如果没有为该列指定明确的值,那么该列将被分配为'0000-00-00 00:00:00',并且不会发生警告。根据是否启用了严格的SQL mode或包含NO_ZERO_DATE的SQL mode,默认值'0000-00-00 00:00:00'可能是不被允许的。

目标库MySQL 5.7.20的explicit_defaults_for_timestamp值是默认的OFF,结合上述规则,就可以模拟复现上述问题了。

虽然原始的建表语句中TIMESTAMP类型字段包含了NULL和DEFAULT属性,但是根据同步软件的错误提示,很明显,不知道什么原因,他在执行的时候忽略了这两个属性,导致真实的执行语句是,

create table test(
  id int not null, 
  createtime timestamp, 
  updatetime timestamp
);

我们在客户端,执行上述SQL,就会得到相同的错误信息,

Invalid default value for 'updatetime'

建表语句第一个TIMESTAMP类型字段是createtime,按照上述规则(1),没有明确使用NULL属性声明的TIMESTAMP列会自动使用NOT NULL属性声明,按照上述规则(2),表中的第一个TIMESTAMP列,如果没有明确地用NULL属性,DEFAULT属性或ON UPDATE属性声明,将自动用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性声明,假设这个建表语句中只有一个TIMESTAMP类型字段createtime,他的结构会改成,

369ff22bb3388d8f7af924fdbc2debb4.png
369ff22bb3388d8f7af924fdbc2debb4.png

按照上述规则(3),在第一个列之后的TIMESTAMP列,即此处的updatetime,如果没有明确地用NULL属性或明确的DEFAULT属性来声明,就会自动声明为DEFAULT '0000-00-00 00:00:00',但是这个MySQL 5.7.20的sql_mode变量包含了NO_ZERO_DATE规则,因此默认值'0000-00-00 00:00:00'不被允许,所以提示了错误,

Invalid default value for 'updatetime'

我们能推断,如果表中存在两个及以上这种情况的TIMESTAMP类型字段,通过这个软件做同步,建表的时候,就会报错,这个可能是软件的一个bug,或者存在其他的配置控制,但就不在数据库的范畴了。

另外,多说一点,原始语句中createtime和updatetime列都指定了默认值,但还是设置NULL属性,这其实就有些矛盾了,或者说是设计上的不严谨,从规范设计开发的角度,还是应该避免的,

create table test(
  id int not null auto_increment, 
  createtime timestamp null default current_timestamp, 
  updatetime timestamp null default current_timestamp on update current_timestamp
);
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2021-11-05 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档