记一次 MySQL timestamp 精度问题的排查 → 过程有点曲折

时间:2024-01-21 11:52:54

开心一刻

下午正准备出门,跟正刷着手机的老妈打个招呼

我:妈,今晚我跟朋友在外面吃,就不在家吃了

老妈拿着手机跟我说道:你看这叫朋友骗缅北去了,tm血都抽干了,多危险

我:那是他不行,你看要是吴京去了指定能跑回来

老妈:还吴京八经的,特么牛魔王去了都得耕地,唐三藏去了都得打出舍利,孙悟空去了都得演大马戏

我:那照你这么说,唐僧师徒取经走差地方了呗

老妈:那可没走错,他当年搁西安出发,他要是搁云南出发呀,上午到缅北,下午他就到西天

我:哈哈哈,那西游记就两级呗,那要是超人去了呢?

老妈:那超人去了,回来光剩超,人留那了

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL

问题复现

我简化下业务与项目

MySQL 8.0.25

spring-boot 2.2.10.RELEASE 搭建 demo :spring-boot-jpa-demo

tbl_user

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_02

测试代码:

/**
 * @description: xxx描述
 * @author: @青石路
 * @date: 2024/1/9 21:42
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserTest {

    @Resource
    private UserRepository userRepository;

    @Test
    public void get() {
        DateTimeFormatter dft = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
        Timestamp lastModifiedTime  = Timestamp.valueOf(LocalDateTime.parse("2024-01-11 09:33:26.643", dft));

        // 1.先保存一个user
        User user = new User();
        user.setUserName("zhangsan");
        user.setPassword("zhangsan");
        user.setBirthday(LocalDate.now().minusYears(25));
        user.setLastModifiedTime(lastModifiedTime);
        log.info("user.lastModifiedTime = {}", user.getLastModifiedTime());
        userRepository.save(user);
        log.info("user 保存成功,userId = {}", user.getUserId());

        // 2.然后再根据id查询这个user
        Optional<User> userOptional = userRepository.findById(user.getUserId());
        if (userOptional.isPresent()) {
            log.info("从数据库查询到的user,user.lastModifiedTime = {}", userOptional.get().getLastModifiedTime());
        }
    }
}

View Code

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_03

这么清晰的代码,大家都能看懂吧?

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_04

我们来看下日志输出

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_05

lastModifiedTime 的值是 2024-01-11 09:33:26.643 ,从数据库查询得到的却是: 2024-01-11 09:33:27.0

是不是被震惊到了?

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_06

曲折排查

MySQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_07

2024-01-11 09:33:27

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_08

这说明数据入库有问题,而不是读取有问题

我们来梳理下数据入库经历了哪些环节

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_09

Spring Data JPA 至 mysql-connector-java

MySQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_10

源码跟踪

Spring Data JPA 与 mysql-connector-java

大家请坐好,我要开始装逼了

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_11

JPA 用的少,一时还不知道从哪里开始去跟源码,但不要慌,楼主有 葵花宝典 :杂谈篇之我是怎么读源码的,授人以渔

断点追踪源码,一时用一时爽,一直用一直爽

userRepository.save(user)

SessionImpl#firePersist

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_12

PersistEventListener::onPersist 了,一路跟下去,会来到 AbstractSaveEventListener#performSaveOrReplicate

里面有如下代码

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_13

Action 的实际类型是: EntityIdentityInsertAction

hibernate 的 事件机制 ,简单来说就是 EntityIdentityInsertAction 的 execute

EntityIdentityInsertAction#execute 跟,会来到 GetGeneratedKeysDelegate#executeAndExtract

重点来了,大家打起精神

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_14

session.getJdbcCoordinator().getResultSetReturn().executeUpdate( insert ) 的 executeUpdate

它长这样

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_15

如果不是断点跟的话

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_16

你知道接下来跟谁吗?

当然,非常熟悉源码的人(比如我),肯定知道跟谁

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_17

但是用了断点,大家都知道跟谁了

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_18

ClientPreparedStatement#executeInternal

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_19

mysql-connector-java ,发送给 MySQL Server 的 SQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_20

last_modified_time 精度没丢

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_21

那问题出在哪?

MySQL

MySQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_22

MySQL 时间精度

MySQL 了,直接执行 SQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_23

哦豁,敢情前面的源码分析全白分析了,我此刻的心情你们懂吗

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_24

MySQL

MySQL 官方文档找找看(注意参考手册版本要和我们使用的 MySQL

search

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_25

The DATE, DATETIME, and TIMESTAMP Types 有这么一段比较关键

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_26

我给大家翻译一下

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_27

继续看 Fractional Seconds in Time Values,内容不多,大家可以通篇读完

MySQL 的 TIME , DATETIME 和 TIMESTAMP

CREATE TABLE t1 (t TIME(3), dt DATETIME(6))

SQL规范规定的默认是 6,MySQL8 默认值取 0 是为了兼容 MySQL 以前的版本

TIME , DATETIME 或 TIMESTAMP 值到相同类型的列时,如果值的小数位与精度不匹配时,会进行四舍五入

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_28

四舍五入的判断位置是精度的后一位,比如精度是 0,则看值的第 1 位小数,来决定是舍还是入,如果精度是 2,则看值的第 3 位小数

简单来说:值的精度大于列类型的精度,就会存在四舍五入,否则值是多少就存多少

当发生四舍五入时,既不会告警也不会报错,因为这就是 SQL 规范

那如果我不像要四舍五入了,有没有什么办法?

MySQL 也给出了支持,就是启用 SQL mode :TIME_TRUNCATE_FRACTIONAL

启用之后,当值的精度大于列类型的精度时,就是直接按列类型的精度截取,而不是四舍五入

MySQL 的锅呀, MySQL

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_29

那是谁的锅?

MySQL

bug

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_30

总结

debug

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_31

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_四舍五入_32

2、MySQL 时间精度

MySQL 的 TIME , DATETIME 和 TIMESTAMP

SQL mode : TIME_TRUNCATE_FRACTIONAL

3、规范

java.sql.Timestamp

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_33

MySQL 开发规范会强调:没有特殊要求,时间类型用 datetime

datetime 可用于分区,而 timestamp 不行,2、 timestamp 的范围只到 2038-01-19 03:14:07.499999

2038-01-19 03:14:07.499999 之后, timestamp

2038

补充

timestamp 不能分区,进行一下补充(感谢 @xiaohuazi 指正)

DATE  和 DATETIME

MySQL 5.7 说明如下

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_SQL_34

MySQL 8.0 说明如下

记一次 MySQL  timestamp 精度问题的排查 → 过程有点曲折_MySQL_35

timestamp 类型的列只能基于 UNIX_TIMESTAMP