Mysql/Doctrine如何查询指定月份是否处于startDate与endDate区间内

核心判断逻辑 #

我们可以按startDate和endDate的年份差拆分三种判断规则，覆盖所有场景：

• 若endDate年份 减去 startDate年份 大于1：两个日期间存在完整自然年，所有12个月份都会被覆盖，直接返回匹配
• 若endDate年份 减去 startDate年份 等于1：目标月份满足≥ MONTH(startDate) 或者 ≤ MONTH(endDate)即匹配
• 若endDate年份 等于 startDate年份：目标月份满足≥ MONTH(startDate) 且 ≤ MONTH(endDate)即匹配

代码示例 #

MySQL 实现（传入单个月份） #

假设传入的目标月份为@target_month，查询匹配的行：

SELECT * FROM road_trip
WHERE 
(
    YEAR(endDate) - YEAR(startDate) > 1
) OR (
    YEAR(endDate) - YEAR(startDate) = 1 
    AND (@target_month >= MONTH(startDate) OR @target_month <= MONTH(endDate))
) OR (
    YEAR(endDate) = YEAR(startDate)
    AND @target_month BETWEEN MONTH(startDate) AND MONTH(endDate)
);

MySQL 实现（传入月份数组，例如[5,6,7]） #

用JSON_OVERLAPS匹配数组中任意符合条件的月份：

SELECT * FROM road_trip
WHERE 
(
    YEAR(endDate) - YEAR(startDate) > 1
) OR (
    YEAR(endDate) - YEAR(startDate) = 1 
    AND JSON_OVERLAPS(
        -- 生成当前区间覆盖的所有月份数组
        JSON_SEQUENCE(MONTH(startDate), 12) UNION JSON_SEQUENCE(1, MONTH(endDate)),
        '[5,6,7]'
    )
) OR (
    YEAR(endDate) = YEAR(startDate)
    AND JSON_OVERLAPS(
        JSON_SEQUENCE(MONTH(startDate), MONTH(endDate)),
        '[5,6,7]'
    )
);

PostgreSQL 实现（传入月份数组） #

SELECT * FROM road_trip
WHERE 
(
    EXTRACT(YEAR FROM endDate) - EXTRACT(YEAR FROM startDate) > 1
) OR (
    EXTRACT(YEAR FROM endDate) - EXTRACT(YEAR FROM startDate) = 1 
    AND (
        EXTRACT(MONTH FROM startDate) <= ANY(ARRAY[5,6,7]) 
        OR EXTRACT(MONTH FROM endDate) >= ANY(ARRAY[5,6,7])
    )
) OR (
    EXTRACT(YEAR FROM endDate) = EXTRACT(YEAR FROM startDate)
    AND EXTRACT(MONTH FROM startDate) <= ANY(ARRAY[5,6,7]) 
    AND EXTRACT(MONTH FROM endDate) >= ANY(ARRAY[5,6,7])
);

性能优化建议 #

• 可以预先计算year_diff = YEAR(endDate) - YEAR(startDate)、start_month、end_month三个字段存入表中，设置联合索引，避免查询时重复调用日期函数，查询效率可以提升数倍
• 数据量超过10万行时，建议创建一个存储1-12所有月份的辅助表，用关联查询替代JSON/数组函数计算，性能提升更明显

内容的提问来源于stack exchange，提问作者Thibssss13