Mysql?DATETIME?毫秒坑的解決

更新時間：2025年01月18日 11:34:19 作者：jianzhangg

本文主要介紹了Mysql?DATETIME?毫秒坑的解決,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

今天寫代碼突發(fā)一個詭異的 bug，代碼邏輯大概如下。

1. 新增退款單記錄
boolean save = shopOrderRefundService.save(shopOrderRefundAdd);

// 2. 調(diào)用京東退款
MiniappRefundResponse response = jdOrderOpenApiService.miniappRefund(...);
if (response.isSuccess()) {
    shopOrderRefundAdd.setStatus(ShopOrderRefundStatusEnum.SYNC_FAIL.getDatabaseCode());
    boolean update = shopOrderRefundService.updateByIdAndStatus(shopOrderRefundAdd);
    //返回失敗
    ...
}

// 3. 更新退款單狀態(tài)調(diào)用成功
shopOrderRefundAdd.setStatus(ShopOrderRefundStatusEnum.JD1001.getDatabaseCode());
boolean update = shopOrderRefundService.updateByIdAndStatus(shopOrderRefundAdd);
...

先生成退款單入庫，再調(diào)京東接口，根據(jù)接口返回值再修改退款單狀態(tài)。
問題是第三步修改的時候，有時成功有時失敗。

本地跑了下，跟事物沒關(guān)系。

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]

拉同事過來一起看，發(fā)現(xiàn)修改的代碼有問題。

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 代碼的時候忘把時間范圍去掉，用單號查就沒問題了。
但就算有時間范圍，創(chuàng)建的時候肯定在修改前，為什么還是查不到呢？
又跑了幾遍發(fā)現(xiàn)時間插入的問題。

注意看時間的毫秒，如果傳入的SQL帶毫秒，MySQL在入庫的時候自動四舍五入了，這導致本來是 07.599 秒的數(shù)據(jù)變成了 08 秒。
但也不對，后面就算是 07.699，如果轉(zhuǎn)成 08 也能查到。
我把更新 SQL 的查詢部分單獨拎出來看。
假設數(shù)據(jù)庫里有只有這條數(shù)據(jù)。 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 能查到數(shù)據(jù)？
答案是前兩個查不到，后三個查得到。

這是查看 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 分析器、優(yōu)化器、執(zhí)行器操作邏輯。

這里面關(guān)于時間的坑很多，一一說。

第一個為什么查不到？

優(yōu)化器先通過 order_no 查詢到這條數(shù)據(jù)，再在優(yōu)化器中直接比較 "condition_value": false，因為查出來 create_at 是 2025-01-15 21:56:08 != 2025-01-15 21:56:07.974。

優(yōu)化器能識別毫秒，插入時候的四舍五入是執(zhí)行器入庫時候轉(zhuǎn)的。優(yōu)化器和執(zhí)行器在必要的時候都會四舍五入，但在這種直接比較的場景沒有轉(zhuǎn)。

第二個為什么查不到？

優(yōu)化器將這個范圍查詢執(zhí)行做成了 "'2025-01-15 21:56:07' < create_at < '2025-01-15 21:56:08'"，自然就查不到了，<= 才查得到。
為了驗證我試了各種范圍 SQL，發(fā)現(xiàn)雖然優(yōu)化器做了四舍五入，但在范圍查詢的時候，< 和 <=，> 和 >=，也根據(jù)毫秒做了區(qū)分。

第三個、第四個為什么查得到？

分析器和優(yōu)化器把第三個 SQL 優(yōu)化成了第四個 SQL，由于是 = 查詢，優(yōu)化器和執(zhí)行器都做了四舍五入成了 08 秒，所以查得到。

第五個直接走 id 查詢自然查的出來。

以上是我的探索過程，很早前聽過 MySQL DATETIME 有坑，我這就是個真實的案例了。
其實吧應該算自己對最底層了解的不夠深刻，分析器、優(yōu)化器、執(zhí)行器的代碼必然是非常復雜的，都是在踩坑中學習。
所以好一點的處理方式，要么換成時間戳，要么帶毫秒，要么用字符串，根據(jù)業(yè)務選擇吧。

到此這篇關(guān)于Mysql DATETIME 毫秒坑的解決的文章就介紹到這了,更多相關(guān)Mysql DATETIME 毫秒坑內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: