简单描述网站建设流程,网站网页优化技巧,wordpress信用卡,网站开发文件夹组织结构同事说他通过某款商业数据同步软件将一个 MySQL 5.7.28 的库同步到 MySQL 5.7.20 的库时#xff0c;如果表中含有 TIMESTAMP 数据类型、缺省值为 current_timestamp 的字段#xff0c;这些表的同步任务就都失败了#xff0c;而另外的一些包含了 DATETIME 数据类型的表就同步…同事说他通过某款商业数据同步软件将一个 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 细致的老司机们可能会注意到日志中记录的 SQL 语句显示 createtime 和 updatetime 都只声明了 TIMESTAMP 类型缺少了原始建表语句中的 NULL 和 DEFAULT 属性这会有什么影响呢
MySQL 中有个explicit_defaults_for_timestamp 变量他决定了 MySQL 是否为 TIMESTAMP 列的默认值和 NULL 值的处理启用某些非标准的行为 如文档所说如果 explicit_defaults_for_timestampOFF 服务器会启用非标准行为并按以下规则处理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 nullcreatetime timestampupdatetime timestamp);我们在客户端执行上述 SQL 就会得到相同的错误信息
Invalid default value 建表语句第一个 TIMESTAMP 类型字段是 createtime 按照上述规则(1)没有明确使用 NULL 属性声明的 TIMESTAMP 列会自动使用 NOT NULL 属性声明按照上述规则(2)表中的第一个 TIMESTAMP列如果没有明确地用 NULL 属性DEFAULT 属性或 ON UPDATE 属性声明将自动用 DEFAULTCURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明假设这个建表语句中只有一个 TIMESTAMP 类型字段 createtime 他的结构会改成 按照上述规则(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’不被允许所以提示了错误
我们能推断如果表中存在两个及以上这种情况的 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
);
