Mysql DATETIME 毫秒坑的解决_Mysql

今天写代码突发一个诡异的 bug，代码逻辑大概如下。

1. 新增退款单记录
boolean save = shoporderrefundservice.save(shoporderrefundadd);

// 2. 调用京东退款
miniapprefundresponse response = jdorderopenapiservice.miniapprefund(...);
if (response.issuccess()) {
    shoporderrefundadd.setstatus(shoporderrefundstatusenum.sync_fail.getdatabasecode());
    boolean update = shoporderrefundservice.updatebyidandstatus(shoporderrefundadd);
    //返回失败
    ...
}

// 3. 更新退款单状态调用成功
shoporderrefundadd.setstatus(shoporderrefundstatusenum.jd1001.getdatabasecode());
boolean update = shoporderrefundservice.updatebyidandstatus(shoporderrefundadd);
...

先生成退款单入库，再调京东接口，根据接口返回值再修改退款单状态。
问题是第三步修改的时候，有时成功有时失败。

本地跑了下，跟事物没关系。

creating a new sqlsession
sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@af21da9] was not registered for synchronization because synchronization is not active
jdbc connection [org.apache.shardingsphere.driver.jdbc.core.connection.shardingsphereconnection@546d31d3] will not be managed by spring
closing non transactional sqlsession [org.apache.ibatis.session.defaults.defaultsqlsession@af21da9]

拉同事过来一起看，发现修改的代码有问题。

public boolean updatebyidandstatus(shoporderrefund shoporderrefund, integer status) {
    datetime date = dateutil.date();
    return update(shoporderrefund,new lambdaupdatewrapper<shoporderrefund>()
            .eq(shoporderrefund::getorderno,shoporderrefund.getorderno())
            .eq(shoporderrefund::getstatus,status)
            .between(shoporderrefund::getcreateat, dateutil.offsetmonth(date, -2), date)
    );
}

copy 代码的时候忘把时间范围去掉，用单号查就没问题了。
但就算有时间范围，创建的时候肯定在修改前，为什么还是查不到呢？
又跑了几遍发现时间插入的问题。

注意看时间的毫秒，如果传入的sql带毫秒，mysql在入库的时候自动四舍五入了，这导致本来是 07.599 秒的数据变成了 08 秒。
但也不对，后面就算是 07.699，如果转成 08 也能查到。
我把更新 sql 的查询部分单独拎出来看。
假设数据库里有只有这条数据。 order_no 是主键，create_at 有索引，create_at 是 datetime 类型，不带毫秒。

select order_no, create_at
from shop_order_refund_202501
where
    order_no = 'sr20250115215607789061'
  and
    create_at between '2025-01-15 21:56:07.974' and '2025-01-15 21:56:07.974';

select order_no, create_at
from shop_order_refund_202501
where
    create_at between '2025-01-15 21:56:07.274' and '2025-01-15 21:56:07.974';

select order_no, create_at
from shop_order_refund_202501
where
    create_at between '2025-01-15 21:56:07.974' and '2025-01-15 21:56:07.974';

select order_no, create_at
from shop_order_refund_202501
where create_at = '2025-01-15 21:56:07.974' ;

select order_no, create_at
from shop_order_refund_202501
where
    order_no = 'sr20250115215607789061';

猜猜哪些 sql 能查到数据？
答案是前两个查不到，后三个查得到。

这是查看 mysql server 层的 trace 的 sql。

set optimizer_trace = "enabled=on";
select order_no, create_at
from shop_order_refund_202501
where
    order_no = 'sr20250115215607789061'
  and
    create_at between '2025-01-15 21:56:07.974' and '2025-01-15 21:56:07.974';
select *
from information_schema.optimizer_trace;
set optimizer_trace = "enabled=off";
show session variables like 'optimizer_trace%';
show global variables like 'optimizer_trace%';

trace 很长我不贴代码了。通过 trace 可以看到 mysql 分析器、优化器、执行器操作逻辑。

这里面关于时间的坑很多，一一说。

第一个为什么查不到？

优化器先通过 order_no 查询到这条数据，再在优化器中直接比较 "condition_value": false，因为查出来 create_at 是 2025-01-15 21:56:08 != 2025-01-15 21:56:07.974。

优化器能识别毫秒，插入时候的四舍五入是执行器入库时候转的。优化器和执行器在必要的时候都会四舍五入，但在这种直接比较的场景没有转。

第二个为什么查不到？

优化器将这个范围查询执行做成了 "'2025-01-15 21:56:07' < create_at < '2025-01-15 21:56:08'"，自然就查不到了，<= 才查得到。
为了验证我试了各种范围 sql，发现虽然优化器做了四舍五入，但在范围查询的时候，< 和 <=，> 和 >=，也根据毫秒做了区分。

第三个、第四个为什么查得到？

分析器和优化器把第三个 sql 优化成了第四个 sql，由于是 = 查询，优化器和执行器都做了四舍五入成了 08 秒，所以查得到。

第五个直接走 id 查询自然查的出来。

以上是我的探索过程，很早前听过 mysql datetime 有坑，我这就是个真实的案例了。
其实吧应该算自己对最底层了解的不够深刻，分析器、优化器、执行器的代码必然是非常复杂的，都是在踩坑中学习。
所以好一点的处理方式，要么换成时间戳，要么带毫秒，要么用字符串，根据业务选择吧。

到此这篇关于mysql datetime 毫秒坑的解决的文章就介绍到这了,更多相关mysql datetime 毫秒坑内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！