数据库毫秒值精度处理，导致创建时间字段大于修改时间

原创

Lucky小黄人 2024-01-22 09:33:01 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者Lucky小黄人的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、问题概述

今天碰到个很奇怪的现象，现象是发现数据库有张表的某几条记录的创建时间大于修改时间，非常奇怪，打工这么多年，第一次碰到这个问题
首先查看 mysql 表结构，怀疑 create_time 字段设置了本应该设置在了 update_time 字段上的 on update CURRENT_TIMESTAMP 配置，但是发现并没有，配置是正确的，create_time 字段没有配置自动更新为当前时间戳，但是更新时间设置。以下是抽象后的表结构

create table employee
(
    id               bigint auto_increment comment '主键id'
        primary key,
    name        varchar(56)     default ''                not null comment '名字',
    create_time      timestamp        default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time      timestamp        default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
) comment '员工表';

二、排查过程

通过查日志，发现3个信息：

当时该行记录先是被创建，随后很快发生了一次修改，
创建记录时修改时间和创建时间是一致的，但是修改完之后创建时间就比修改时间大了。
insert 语句指定了 create_time 和 update_time 字段的值，并且时间精度是毫秒，但是修改时没有指定 update_time 的值

下面是 gorm 框架打印的 insert 语句和 update 语句

-- insert 语句
INSERT INTO `employee` (`name`,`create_time`,`update_time`) VALUES ('张三','2024-01-15 09:39:08.633','2024-01-15 09:39:08.633');Rows:1 # insert 时我们主动设置了创建时间和修改时间
-- update 语句
UPDATE `employee`  set `name` = '王五';Rows:1 // update 没有主动设置修改时间

根据上面的信息，我们看到 insert 语句的时间精度是毫秒，但是数据库中显示的时间精度是秒，创建时间值是 2024-01-15 09:39:09，修改时间值是 2024-01-15 09:39:08，所以我们猜测是数据库对我们传入的时间进行了四舍五入，并且由于数据库实例跟业务服务实例之间存在时钟差，可能数据库比业务实例时钟小几百毫秒。猜测完整过程可能是下面这样:

创建由于我们主动设置了 create_time 和 update_time 两个字段的值，所以数据库会无脑使用我们传入的，由于精度不一样，所以数据库进行了四舍五入，保存了 2024-01-15 09:39:09，更新语句并没有主动设置更新时间，所以此时数据库会使用自己操作系统的当前时间作为修改时间，而由于数据库实例的时钟略小于业务实例，所以可能更新的时候数据库时间恰好小于 2024-01-15 09:39:09.500，经过四舍五入后，设置了修改时间为 2024-01-15 09:39:08

根据上面掌握的信息和猜测，我们决定先看下当时两个 sql 执行时产生的 binlog 进行验证。 binlog 如下所示，根据 binlog，似乎我们的猜测可以解释的通，先主动设置时间参数后发生四舍五入，随后因为时钟问题，update_time 被更新成了一个更早的时间。

set names utf8mb4;
-- time 2024-01-15 09:39:08 file mysql-bin.000384 postion 65214812
insert /* mysql-flashback `my_test_db`.`employee` */ into `my_test_db`.`employee` (`id`, `name`, `create_time`, `update_time`) values ('198', '张三', '2024-01-15 09:39:09', '2024-01-15 09:39:09');
-- time 2024-01-15 09:39:08 file mysql-bin.000384 postion 652149574 
update /* mysql-flashback `my_test_db`.`employee` */ `my_test_db`.`employee` set `id`='198', `arrive_id`='王五', `create_time`='2024-01-15 09:39:09', `update_time`='2024-01-15 09:39:08');

本来我们以为原因就是上面这个，但是经过请教 dba，发现只才对了一半，前半段确实是因为主动设置了时间参数且发生了四舍五入，导致时间记录为 2024-01-15 09:39:09，但是后半部分，修改时间设置为 2024-01-15 09:39:08 却不是因为时钟差问题，而是因为在业务没有设置时间字段时，数据库会默认采用操作系统时间，并且采用截断算法，直接忽略毫秒值，通过观察上面 2 条 binlog 的执行时间，可以看到两条 sql 都是在 2024-01-15 09:39:08 执行完成的，说明执行 update 语句时还没有到 2024-01-15 09:39:09，所以修改时间字段被截断设置为了 2024-01-15 09:39:08。