MySQL的Timestamp準確度、快取應用的雷點

MySQL是常用的關聯式資料庫管理系統，其中Timestamp類型是一種用來儲存日期和時間的資料類型。在定義Timestamp欄位時，如果不定義準確小數點的話，預設只到秒，且會四捨五入。

比如以下的資料表定義，就明確定義了到小數點下6位也就是微秒精度。

CREATE TABLE `services` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT,  
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  
`end_time` timestamp(6),  
`name` varchar(255) NOT NULL,  
`created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  
`updated_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),  
PRIMARY KEY (`id`)  
)

以下的定義就沒有明確定義，所以是到秒。本文的範例是以這個為主。

CREATE TABLE `services` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT,  
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
`end_time` timestamp,  
`name` varchar(255) NOT NULL,  
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
PRIMARY KEY (`id`)  
)

若要調整成捨棄小數位，可以使用MySQL的參數設定：time_truncate_fractional。該設定可以用來設置Timestamp的小數點精度。如果將此參數設置為ON，則MySQL會捨棄Timestamp值的小數位，而不是四捨五入。

SET sql_mode='TIME_TRUNCATE_FRACTIONAL';

在使用MySQL Timestamp時，可能會遇到大小比較的問題。例如，當從資料庫中讀取一筆Timestamp資料並與Java裡的時間進行比較時，可能會出現比較錯誤的情況。這是因為MySQL Timestamp的四捨五入可能導致大小關係相反。

用剛剛的Services資料表做比喻，這也類似我實際上遇到的問題。這邊定義我們的邏輯，用現在時間跟DB讀出來的 startTime endTime 比較，isEnabled 會回傳現在該服務的狀態。

public class Service {

    private Long id;
    private String name;
    private LocalDateTime startTime;
    private LocalDateTime endTime;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;

    public boolean isEnabled() {
        LocalDateTime now = LocalDateTime.now();
        if (startTime.isAfter(now)) {
            // Current time is prior to startTime
            return false;
        } else if (endTime == null || endTime.isAfter(now)) {
            // Current time is between startTime and endTime, or endTime is null
            return true;
        } else {
            // Current time is after endTime
            return false;
        }
    }
}

範例資料如下:

INSERT INTO `services` (`id`, `start_time`, `name`, `created_at`, `updated_at`)
VALUES (1, '2022-02-25 12:00:00', 'credit_card', NOW(), NOW());

select * from services;
+----+---------------------+--------------+---------------------+---------------------+---------------------+
| id | start_time          | name | end_time            | created_at          | updated_at          |
+----+---------------------+--------------+---------------------+---------------------+---------------------+
|  1 | 2022-02-25 12:00:00 | credit_card  | NULL                | 2022-02-25 10:00:00 | 2022-02-25 10:00:00 |
+----+---------------------+--------------+---------------------+---------------------+---------------------+

假設現在時間是是 2023-02-25 12:00:04.500，我們想結束某個服務，我們把end_time設成現在時間，sql上會被存成 2023-02-25 12:00:05。注意到這個 timestamp 已經被進位到了整秒，而且精度是秒級的。

假設馬上有另一個請求進到資料庫查找，時間是 2023-02-25 12:00:04.600 ，這時候這個 isEnabled 的結果就會還是 true 。這樣會出現service還沒結束的錯誤結果，雖然這只會持續幾百毫秒，但因為原本設計是快取 API Response，也就是只快取計算後的 isEnabled，導致接下來的更長時間 API 都回傳錯誤結果。如果 timestamp 精度較高，這種錯誤的機率會變小，但我們後來發現即使解決了比較問題，還有另一個問題需要注意，那就是不同主機上的時間可能會有幾百毫秒的差異，甚至更高。

因此，如果把比較Timestamp的結果存在快取中，則可能會導致錯誤的結果。解決方法是把判斷邏輯寫在應用層，在快取中我們直接儲存原本的 startTime 和 endTime 。這才能根本解決問題。

總結如下三點:

MySQL 的 timestamp 如果沒有特別指定是準確到秒
MySQL 可以用這個格式定義精度: timestamp(6) 接受的精度數字是 0 到 6
不要快取會因為時間差異而結果不一樣的資料

P.S. 這篇網誌的程式碼及一部分文字是 ChatGPT 生成

炸蝦碎碎念

炸蝦碎碎念

MySQL的Timestamp準確度、快取應用的雷點