Mysql DATETIME 毫秒坑的解决！

admin • • SQL技巧

Mysql DATETIME 毫秒坑的解决！

   作者：jianzhangg
本文主要介绍了Mysql DATETIME 毫秒坑的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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]

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

1
2
3
4
5
6
7
8
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 代码的时候忘把时间范围去掉，用单号查就没问题了。
但就算有时间范围，创建的时候肯定在修改前，为什么还是查不到呢？
又跑了几遍发现时间插入的问题。

2025118111854612

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

pE2OfjH.png

1
2
3
4
5
6
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';
1
2
3
4
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';
1
2
3
4
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';
1
2
3
select order_no, create_at
from shop_order_refund_202501
where create_at = '2025-01-15 21:56:07.974' ;
1
2
3
4
select order_no, create_at
from shop_order_refund_202501
where
    order_no = 'SR20250115215607789061';

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

pE2O4ud.png

这是查看 MySQL Server 层的 Trace 的 SQL。

1
2
3
4
5
6
7
8
9
10
11
12
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 毫秒坑的解决的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>