MySQL 由於 Java 日期 LocalDateTime 資料精度引發的線上問題

語言: CN / TW / HK

(給 ImportNew 加星標,提高Java技能)

最近在公司開發的專案是對賬系統,其中就包括每日(T+1)對賬。 每日對賬就是對前一日期產生的資料核對。 舉個具體的例子就是: 在第三方支付公司,裡面會產生支付裡面的支付訂單與下載渠道(微信、支付或者其它第三方支付)的每日交易賬戶需要進行對比。 關於微信支付的下載交易賬單地址。 支付寶或者其它的下載交易單內容類似,只是檔案格式不一樣。

我們來看一下對賬的資料流向:

今天要說的是在查詢我們儲存的第三方支付支付訂單與微信支付的交易賬單進行對比的時候,在查詢我們 T 日資料的時候多查詢出來了資料導致資料對不平。下面我把資料庫的對賬表的簡化模型簡單列舉一下:

對賬資料表

create table trade_record (
id BIGINT(20) AUTO_INCREMENT COMMENT '主鍵',
source_no varchar(32) not null DEFAULT '' COMMENT '資料來源(微信、交易系統、支付寶等)',
order_no VARCHAR(64) not null DEFAULT '' COMMENT '訂單號',
compare_no VARCHAR(64) not null DEFAULT '' COMMENT '對比號',
amount DECIMAL(20,4) not null default '0' COMMENT '金額',
trade_at DATETIME not null COMMENT '交易成功時間',
create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
PRIMARY KEY (`id`),
index idx_trade_at(trade_at),
index idx_create_at(create_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '交易記錄表';

我們先插入幾條資料用於查詢測試:

待對賬資料

-- 支付系統訂單
insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('payment', '111111', '111111', '100', '2022-05-10 02:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('payment', '222222', '222222', '200', '2022-05-10 05:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('payment', '333333', '333333', '300', '2022-05-10 12:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('payment', '444444', '444444', '400', '2022-05-10 23:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('payment', '555555', '555555', '500', '2022-05-11 00:00:10');


-- 渠道返回訂單
insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('wechat_pay', '111111', '111111', '100', '2022-05-10 02:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('wechat_pay', '222222', '222222', '200', '2022-05-10 05:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('wechat_pay', '333333', '333333', '300', '2022-05-10 12:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('wechat_pay', '444444', '444444', '400', '2022-05-10 23:23:43');


insert into trade_record(source_no, order_no, compare_no, amount, trade_at)
values ('wechat_pay', '555555', '555555', '500', '2022-05-11 00:00:00');

插入完成後,總共 10 條資料:

比如我們需要比較 2022-05-10 的資料的時候,那些我們只需要查詢出時間範圍為:2022-05-10 00:00:00 ~ 2022-05-10 23:59:59 的微信支付以及支付平臺的資料然後按照 compare_no 進行逐條對比就行了。

支付系統資料:

渠道返回資料:

查詢出來兩方資料,我們就可以根據 compare_no 進行逐一比對,可以得到兩方的對賬是可以對平的。雖然我們是這樣設計的,然後我們驗證也沒有問題。但是在 線上出現了對賬不匹配的情況

DateUtils.java

public abstract class DateUtils {


/**
* 昨天的開始時間
* @return
*/
public static LocalDateTime yesterdayDateBegin() {
LocalDate currentTime = LocalDate.now();
LocalDate yesterday = currentTime.plusDays(-1);
return LocalDateTime.of(yesterday, LocalTime.MIN);
}


/**
* 昨天的結束時間
* @return
*/
public static LocalDateTime yesterdayDateEnd() {
LocalDate currentTime = LocalDate.now();
LocalDate yesterday = currentTime.plusDays(-1);
return LocalDateTime.of(yesterday, LocalTime.MAX);
}


}

以上是日期工具類,在單元測試的時候沒有問題。但是上面的工具類得到的開始結束時間,以 2022-05-10 為例:

昨日開始時間:2022-05-09T00:00
昨日結束時間:2022-05-09T23:59:59.999999999

然後以上面的時間進行查詢的得到的結果是:

結果查詢出來了一條交易成功時間為:2022-05-11 00:00:00 的資料,與我們的期望不符。我們可以檢視 Mysql 官網對於 Datetime 這個日期型別的描述。可以看到 Datetime 這個日期型別的精度為小數點後面 6 位 。而我們在 查詢的時候使用的是 9 位 。於是我就使用不同的精度的結束時間進行查詢:

  • 使用 6 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 6 個 9 進行查詢,查詢總條數為 4 條,符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 7 個 9 進行查詢,查詢總條數為 5 條,不符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9999991 進行查詢,查詢總條數為 4條,符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9999992 進行查詢,查詢總條數為 4條,符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9999993 進行查詢,查詢總條數為 4條,符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9999994 進行查詢,查詢總條數為 4條,符合期望。

  • 使用 7 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9999995 進行查詢,查詢總條數為 5條,不符合期望。

  • 使用 8 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 8 個 9 進行查詢,查詢總條數為 5 條,不符合期望。

  • 使用 9 位精度的結束時間查詢:也就是 2022-05-10 23.59.59. 新增 9 個 9 進行查詢,查詢總條數為 5 條,不符合期望。

所以我們可以得出結論: 在使用 MySQL datetime 進行資料查詢的時候只能使用正確的精度才能得出符合期望的資料。並且當使用 datetime 大於精度的時候是進行四捨五入的

建議大家在查詢一天內的資料的時候使用 左關右開 模式也就是:

時間 >= ‘2022-05-10 00:00:00’ 並且 時間 < ‘2022-05-11 00:00:00’

參考文章

https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

轉自:carl-zhao,

連結: blog.csdn.net/u012410733/article/details/124678331

- EOF -

看完本文有收穫?請轉發分享給更多人

關注「ImportNew」,提升Java技能

點贊和在看就是最大的支援 :heart: