MySQL ON DUPLICATE KEY UPDATE 實戰與 Java 月份計算邏輯詳解

MySQL ON DUPLICATE KEY UPDATE 實戰與 Java 月份計算邏輯詳解

本文結合兩個高頻開發場景：MySQL 冪等寫入 (存在更新/不存在插入) 與 Java Calendar 月份邊界計算，給出生產級代碼模板與避坑指南。

一、MySQL ON DUPLICATE KEY UPDATE：冪等寫入模式

核心語法

INSERT INTO invite_rejectlog_num (player_id, month, count, last_modify_time)
VALUES (#{playerId}, #{month}, #{count}, #{lastModifyTime})
ON DUPLICATE KEY UPDATE count = count + 1;

前置條件

• 表必須有 唯一約束 (Primary Key 或 Unique Index) 覆蓋衝突判斷列。
• 示例建表：

CREATE TABLE invite_rejectlog_num (
    player_id BIGINT NOT NULL,
    month INT NOT NULL COMMENT 'YYYYMM',
    count INT NOT NULL DEFAULT 0,
    last_modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (player_id, month)  -- 唯一約束：同玩家同月僅一行
) ENGINE=InnoDB;

行為詳解

進階用法：VALUES() 函數引用插入值

Calendar cal = Calendar.getInstance();
// cal.get(Calendar.MONTH) 返回 0-11 (0=1月, 11=12月)
// cal.set(Calendar.MONTH, 0) 設置為 1月
// cal.add(Calendar.MONTH, -1) 正確向前推算月份

完整工具類：月份邊界、比較、遍歷

import java.util.Calendar;
import java.util.Date;

public class MonthUtils {

    // 獲取某月第一天 00:00:00
    public static Date getMonthStart(int year, int month) { // month 1-12
        Calendar cal = Calendar.getInstance();
        cal.set(year, month - 1, 1, 0, 0, 0);
        cal.set(Calendar.MILLISECOND, 0);
        return cal.getTime();
    }

    // 獲取某月最後一天 23:59:59.999
    public static Date getMonthEnd(int year, int month) {
        Calendar cal = Calendar.getInstance();
        cal.set(year, month - 1, 1, 0, 0, 0);
        cal.add(Calendar.MONTH, 1);        // 下月 1 號
        cal.add(Calendar.MILLISECOND, -1); // 上月最後 1 毫秒
        return cal.getTime();
    }

    // 格式化為 YYYYMM
    public static String formatYM(Date date) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        return String.format("%04d%02d", cal.get(Calendar.YEAR), cal.get(Calendar.MONTH) + 1);
    }

    // 遍歷區間內所有月份 (包含首尾)
    public static void iterateMonths(Date start, Date end, MonthConsumer consumer) {
        Calendar calStart = Calendar.getInstance();
        calStart.setTime(start);
        calStart.set(Calendar.DAY_OF_MONTH, 1);
        calStart.set(Calendar.HOUR_OF_DAY, 0);
        calStart.set(Calendar.MINUTE, 0);
        calStart.set(Calendar.SECOND, 0);
        calStart.set(Calendar.MILLISECOND, 0);

        Calendar calEnd = Calendar.getInstance();
        calEnd.setTime(end);
        calEnd.set(Calendar.DAY_OF_MONTH, 1);

        while (!calStart.after(calEnd)) {
            consumer.accept(calStart.get(Calendar.YEAR), calStart.get(Calendar.MONTH) + 1);
            calStart.add(Calendar.MONTH, 1);
        }
    }

    @FunctionalInterface
    public interface MonthConsumer {
        void accept(int year, int month);
    }

    // 測試主方法
    public static void main(String[] args) {
        // 示例：刪除緩存中 2023 年之前的月份數據
        Calendar calDel = Calendar.getInstance();
        calDel.set(Calendar.MONTH, 0);           // 今年 1 月
        calDel.add(Calendar.MONTH, -1);          // 去年 12 月
        int maxDelMonth = calDel.get(Calendar.MONTH) + 1; // 12

        int cacheMinMonth = 8; // 緩存最小月份 (8月)

        Calendar calCache = Calendar.getInstance();
        calCache.set(Calendar.MONTH, cacheMinMonth - 1);

        Calendar calBoundary = Calendar.getInstance();
        calBoundary.set(Calendar.MONTH, calDel.get(Calendar.MONTH));

        System.out.println("需刪除月份上限: " + maxDelMonth);
        System.out.println("緩存最小月份: " + cacheMinMonth);

        // 正確比較：使用 Calendar.compareTo (按時間線)
        for (; !calCache.after(calBoundary); calCache.add(Calendar.MONTH, -1)) {
            int ym = calCache.get(Calendar.YEAR) * 100 + calCache.get(Calendar.MONTH) + 1;
            System.out.println("刪除緩存月份: " + ym);
        }
    }
}

關鍵比較邏輯解析