MySQL中踩到的坑之timestamp数据类型

啊山022324
197 阅读14分钟

问题描述

在给客户做同城两中心灾备的项目中，使用到数据同步工具来进行数据同步，即源端和目标端通过数据同步工具进行数据同步，当源端故障后，业务可以快速的切换到目标端，在保障源端和目标端数据一致的情况下，目标端能够继续的提供读写服务。在源端MySQL 5.7.25版本中执行如下语句：

ALTER TABLE supp_logistics_information ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间';

update supp_logistics_information set actual_delivery_date='2024-12-19' where logistics_information_id = 1739;

后发现源端和目标端的actual_delivery_date字段的数据不一致。 模拟场景的测试语句：

create table sup1(logistics_information_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '物流信息ID' primary key,order_invoice_id bigint(20) DEFAULT NULL COMMENT '发货单ID');

insert into sup1 values(1739,1111);

insert into sup1 values(1740,2222);

insert into sup1 values(1741,3333);

ALTER TABLE sup1 ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间';

update sup1 set actual_delivery_date='2024-12-19' where logistics_information_id = 1739;

测试用到的语句：

show variables like '%timestamp%';

show create table supp_logistics_information\G

ALTER TABLE sup1 DROP COLUMN actual_delivery_date;

select * from sup1 where logistics_information_id = 1739\G

验证mysql中的行为

注意：在timestamp列中，default值，为了方便测试用的是current_timestamp，但如果使用主从复制架构或者通过数据同步工具同步数据时，会出现主从数据不一致的情况，因为获取的时当前的时间，如果主备延迟较大或者目标端执行的DDL语句的时间滞后了或者主从、源和目标时间不同步的情况执行了，就会出现该种情况。强烈不建议引current_timestamp用做default值。

OFF + 无not null或者null + 无default值

通过排查分析：

mysql源端sql：

ALTER TABLE supp_logistics_information ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间';

update supp_logistics_information set actual_delivery_date='2024-12-19' where logistics_information_id = 1739;

数据同步工具同步给目标端的sql：

ALTER TABLE supp_logistics_information ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间';

update supp_logistics_information set ...全字段...actual_delivery_date='2024-12-19' where ...全字段...logistics_information_id = 1739;

理论上是一致的。但这里有一些细节，需要加一些条件才能是理论上一致，而这些细节如果不注意，也正是造成数据不一致的根因。

在mysql中，执行如上的alter table语句时，会出现这样的情况

当前session中的系统变量explicit_defaults_for_timestamp=OFF的时候，执行ALTER TABLE supp_logistics_information ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间'; mysql的行为是给该字段加上not null，然后还会自动使用 DEFAULT CURRENT_TIMESTAMP和 ON UPDATE CURRENT_TIMESTAMP属性声明。这样的情况就会影响到加该列的值，与预期情况不一致。用户预期为加了该列后，值为null，但是实际上的值是当前的时间。

操作截图如下：

image.png 此时alter table之后的语句传入到目标端来执行，也就是目标端actual_delivery_date字段的值也变成了当前时间，数据同步工具给到目标端执行update语句的时候，此时数据同步工具（解析binlog，binlog里只有DDL语句，并没有记录表数据的变化）是不知道actual_delivery_date字段的值变成了当前时间的，还认为是null值，所以update时，where条件中其中一个条件actual_delivery_date传入的值为null，发现找不到该行记录，update之后影响行数为0，最终就导致了源端和目标端数据不一致的情况。

继续深入后，还发现新的问题

通过官方文档查阅，在explicit_defaults_for_timestamp系统变量中看到：

dev.mysql.com/doc/refman/…

image.png 发现这个是mysql的非标准行为。继续往下将explicit_defaults_for_timestamp=ON后可以禁用非标准行为。

ON + 无not null或者null + 无default值

此时继续验证：

先删除actual_delivery_date列，再添加：

image.png

添加actual_delivery_date列，并查看表数据：

show variables like '%timestamp%';

show create table sup1\G

ALTER TABLE sup1 ADD COLUMN actual_delivery_date timestamp COMMENT '实际发运时间';

show create table sup1\G

select * from sup1;

image.png

修改explicit_defaults_for_timestamp=ON后，actual_delivery_date的值为null，符合预期。

ON + not null + 无default值

前景提示：这里为了测试验证mysql行为，正常情况下，如果定义列为not null，是需要填写默认值的，这个也是规范。

再继续执行如下操作：

ALTER TABLE sup1 ADD COLUMN actual_delivery_date timestamp NOT NULL COMMENT '实际发运时间';

update sup1 set actual_delivery_date=now() where logistics_information_id = 1739;

此时这个操作是加一个字段，not null，但没有默认值，此时mysql的行为是：明确声明了 NOT NULL 属性且没有明确 DEFAULT 属性的 TIMESTAMP 列将被视为没有默认值。则该列将使用隐式默认值“0000-00-00 00:00:00”声明，并会出现警告。这类似于 MySQL 处理其他时间类型（如 DATETIME）的方式。

show create table sup1\G

ALTER TABLE sup1 ADD COLUMN actual_delivery_date timestamp NOT NULL COMMENT '实际发运时间';

select * from sup1;

select @@sql_mode;

insert into sup1 values(1742,6666,'0000-00-00 00:00:00');

image.png

image.png

当前actual_delivery_date的值就为“0000-00-00 00:00:00”，但是当前sql_mode的值中有NO_ZERO_DATE的值，那么手动插入“0000-00-00 00:00:00”是会报错的，但是mysql自己写入就不会报错。不是，哥们！！！mysql不允许用户这样操作，但是mysql自己又能进行该操作，互相矛盾。其实插入“0000-00-00 00:00:00”是可以换个角度理解，该列中定义为not null，但又没有给default默认值，那么mysql其实并不知道该传什么进去，就传了“0000-00-00 00:00:00”。

官方文档对该参数的介绍：

dev.mysql.com/doc/refman/…

image.png

提示：官方文档介绍中的SQL严格模式，是否启用，是指这里sql_mode中STRICT_TRANS_TABLES或STRICT_ALL_TABLES的值，但STRICT_TRANS_TABLES或STRICT_ALL_TABLES的是影响的是修改数据，也就是insert、update的DML语句，而DDL语句是没有出现warning或者error的，和文中的例子对的上。

到这文章的内容分布情况是这个思路来写的：先根据问题，分析排查，然后解决，继续顺着客户行为往下走，继续发现新问题，然后解决，最后扩展问题进行测试验证。

多种情况组合

从explicit_defaults_for_timestamp的值与timestamp数据类型的声明有多种情况：

前面已经列举了三种：

OFF + 无not null或者null + 无default值

ON + 无not null或者null + 无default值

ON + not null + 无default值

共有12情况：

OFF和ON，共两种；

不指定not null或null，not null，null，共三种；

有default值，无default值，共三种。

组合起来：233=12。

还有如下9种情况：

OFF + not null + 无default值

OFF + 无not null或无null + 有default值

OFF + not null + 有default值

OFF + null + 有default值

OFF + null + 无default值

ON + 无not null或无null + 有default值

ON + null + 有default值

ON + null + 无default值

ON + not null + 有default值

注意：本文如果sql_mode没有做特殊说明，默认值为：

image.png

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

OFF + 不指定not null或者null + 无default值 - 扩展验证

该类型还可以划分为两种情况：

第一种，sql_mode模式中无NO_ZERO_DATE，

第二种，sql_mode模式中有NO_ZERO_DATE。

第一种情况，sql_mode模式中无NO_ZERO_DATE

show variables like '%timestamp%';

select @@sql_mode;

create table sup2(id int);

alter table sup2 add column c1 timestamp;

show create table sup2\G

alter table sup2 add column c2 timestamp;

show create table sup2\G

alter table sup2 add column c3 timestamp;

show create table sup2\G

insert into sup2(id) values(1);

select * from sup2;

show create table sup2\G

insert into sup2 values(2,null,null,null);

select * from sup2;

image.png

image.png

image.png 结论为：

  • 未使用 NULL 属性明确声明的 TIMESTAMP 列将自动使用 NOT NULL 属性声明。允许为此类列分配 NULL 值，并将该列设置为当前时间戳。
  • 如果表中的第一个 TIMESTAMP 列未使用 NULL 属性或显式 DEFAULT 或 ON UPDATE 属性明确声明，则将自动使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明。
  • 如果第一个 TIMESTAMP 列之后的列未使用 NULL 属性或显式 DEFAULT 属性明确声明，则将自动声明为 DEFAULT '0000-00-00 00:00:00'（“零”时间戳）。对于未指定此类列的显式值的插入行，该列将被分配“0000-00-00 00:00:00”，并且不会出现警告。

第二种情况，sql_mode模式中有NO_ZERO_DATE

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

show variables like '%timestamp%';

select @@sql_mode;

create table sup3(id int);

alter table sup3 add column c1 timestamp;

show create table sup3\G

alter table sup3 add column c2 timestamp;

alter table sup3 add column c2 timestamp not null;

alter table sup3 add column c2 timestamp default current_timestamp;

show create table sup3\G

insert into sup3 values(1,null,null);

select * from sup3;

image.png

image.png 结论：

  • 未使用 NULL 属性明确声明的 TIMESTAMP 列将自动使用 NOT NULL 属性声明。允许为此类列分配 NULL 值，并将该列设置为当前时间戳。
  • 如果表中的第一个 TIMESTAMP 列未使用 NULL 属性或显式 DEFAULT 或 ON UPDATE 属性明确声明，则将自动使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明。
  • 当启动sql_mode中的NO_ZERO_DATE时，在第一个timestamp列之后的timestamp列需要加上默认值，添加列失败，报默认值无效，因为在第一个timestamp列之后，如不加默认值，mysql分配“0000-00-00 00:00:00”给到该列，但是“0000-00-00 00:00:00”值与sql_mode的NO_ZERO_DATE模式相违背，所以mysql不能分配该值。

官方文档介绍：

dev.mysql.com/doc/refman/…

image.png 综上的两种情况验证结果，与官方文档介绍内容吻合。

OFF + not null + 无default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup6(id int);

insert into sup6 values(1),(2);

alter table sup6 add column c1 timestamp not null;

select * from sup6;

show create table sup6\G

insert into sup6 values(3,null);

insert into sup6 values(4,null);

select * from sup6;

image.png 结论： 如果表中的第一个 TIMESTAMP 列未使用 NULL 属性或显式 DEFAULT 或 ON UPDATE 属性明确声明，则将自动使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明。

OFF + 不指定not null或null + 有default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup7(id int);

insert into sup7 values(1),(2);

alter table sup7 add column c1 timestamp default current_timestamp;

select * from sup7;

show create table sup7\G

insert into sup7 values(3,null);

insert into sup7 values(4,null);

select * from sup7;

image.png 结论：

表中TIMESTAMP 列未使用 NULL 属性时，有显式 DEFAULT 属性明确声明，会使用给到的属性声明，并为该列分配not null。

OFF + not null + default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup8(id int);

insert into sup8 values(1),(2);

alter table sup8 add column c1 timestamp not null default current_timestamp;

select * from sup8;

show create table sup8\G

insert into sup8 values(3,null);

insert into sup8 values(4,null);

select * from sup8;

image.png 结论：

表中TIMESTAMP 列，显式指定not null和default时，该列会正常分配设置。

OFF + null + 有default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup13(id int);

insert into sup13 values(1),(2);

alter table sup13 add column c1 timestamp null default current_timestamp;

select * from sup13;

show create table sup13\G

insert into sup13(id) values(3),(4);

select * from sup13;

image.png 结论：

表中TIMESTAMP 列，显式指定null和default时，该列会正常分配设置。当插入数据不指定timestamp列的值时，会以default值写入该列。

OFF + null + 无default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup14(id int);

insert into sup14 values(1),(2);

alter table sup14 add column c1 timestamp null;

select * from sup14;

show create table sup14\G

insert into sup14(id) values(3),(4);

select * from sup14;

image.png 结论：表中TIMESTAMP 列，显式指定null，不显式指定default时，该列会分配null为default默认值。当插入数据不指定timestamp列的值时，会以default值写入该列。

ON + 不指定not null或null + default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup9(id int);

insert into sup9 values(1),(2);

alter table sup9 add column c1 timestamp default current_timestamp;

select * from sup9;

show create table sup9\G

insert into sup9 values(3,null),(4,null);

insert into sup9(id) values(5),(6);

select * from sup9;

image.png

image.png 结论：

表中TIMESTAMP 列未使用 NULL 或NOT NULL属性，但有显式 DEFAULT 属性明确声明时，会使用给到的属性声明，并为该列分配允许为NULL。当插入数据不指定timestamp列的值时，会以default值写入该列。

ON + null + 有default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup11(id int);

insert into sup11 values(1),(2);

alter table sup11 add column c1 timestamp null default current_timestamp;

select * from sup11;

show create table sup11\G

insert into sup11(id) values(3),(4);

select * from sup11;

image.png 结论：

表中TIMESTAMP 列，显式指定null和default时，该列会正常分配设置。当插入数据不指定timestamp列的值时，会以default值写入该列。

ON + null + 无default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup12(id int);

insert into sup12 values(1),(2);

alter table sup12 add column c1 timestamp null;

select * from sup12;

show create table sup12\G

insert into sup12(id) values(3),(4);

select * from sup12;

image.png 结论：表中TIMESTAMP 列，显式指定null，不显式指定default时，该列会分配null为default默认值。当插入数据不指定timestamp列的值时，会以default值写入该列。

ON + not null + default值

show variables like '%timestamp%';

select @@sql_mode;

create table sup10(id int);

insert into sup10 values(1),(2);

alter table sup10 add column c1 timestamp not null default current_timestamp;

select * from sup10;

show create table sup10\G

insert into sup10(id) values(3),(4);

select * from sup10;

image.png 结论：

表中TIMESTAMP 列，显式指定not null和default时，该列会正常分配设置。

多种组合情况测试结果

  • OFF + 无not null或者null + 无default值 结果：add column后，在该列上会写入当前时间，并且加上ON UPDATE CURRENT_TIMESTAMP属性。
  • ON + 无not null或者null + 无default值 结果：add column后，在该列上的值为NULL。
  • ON + not null + 无default值 结果：add column后，在该列上会写入"0000-00-00 00:00:00"，即使sql_mode种的值有NO_ZERO_DATE。
  • OFF + not null + 无default值 结果：add column后，在该列上会写入当前时间，并且加上ON UPDATE CURRENT_TIMESTAMP属性。
  • OFF + 无not null或无null + 有default值 结果：add column后，有NOT NULL属性，在该列上的值为default值。
  • OFF + not null + 有default值 结果：add column后，有NOT NULL属性，在该列上的值为default值。
  • OFF + null + 有default值 结果：add column后，有NULL属性，在该列上的值为default值。
  • OFF + null + 无default值 结果：add column后，有NULL属性，default 为NULL。
  • ON + 无not null或无null + 有default值 结果：add column后，有NULL属性，在该列上的值为default值。
  • ON + null + 有default值 结果：add column后，有NULL属性，在该列上的值为default值。
  • ON + null + 无default值 结果：add column后，有NULL属性，default 为NULL。
  • ON + not null + 有default值 结果：add column后，有NOT NULL属性，在该列上的值为default值。

官方文档对explicit_defaults_for_timestamp参数的提示

image.png 不推荐使用显式默认的 TIMESTAMP 行为，因为它的唯一目的是允许控制不推荐使用的 TIMESTAMP 行为，这些行为将在 MySQL 的未来版本中删除。当删除这些行为时，explicit_defaults_for_timestamp 不再有任何用途，您也可以预期它也将被删除。

规范建议

为了创建timestamp列时符合用户预期，建议把系统变量设置为explicit_defaults_for_timestamp=ON；杜绝非标准行为，即使设置为ON，还需注意在timestamp列指定not null时，要带上default值，这是才真正的符合规范。

引用

dev.mysql.com/doc/refman/…

avatar
文章
阅读
粉丝